Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014

    Unanswered: sql server back end w/ ms access front end - stored procedures vs linked tables

    Hi, I have developed a database in SQL server and a front end in MS Access using linked tables via ODBC. Everything works great and it is just an internal database for around 20 customers.

    My colleague who is a SQL expert has informed me that I should not allow direct access to any tables and should use stored procs for everything. I take his point however I intend to lock it down so user has only access to forms and not tables AND using stored procs means I will need to control everything in code - adding records, retrieving records - dealing with subforms etc. To me this sounds like A LOT of extra work and I just want to get another opinion on the best practice - should I do as he says and rewrite all the standard database functions to use stored procs for everything? Is this the normal method?

    Many thanks for any help

    Jenny B

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    These are my opinions, take them for what they are worth. You may also want to consult the DBForums Microsoft Access Forum to get their opinions on the topic. Those might be more useful than starting at the back end!

    The decision to use stored procedures instead of application access depends more on how secure you need the database to be versus how easy you want development to be. If you can't secure the infrastructure (in other words keeping the database server secure, the application code secure, etc. then the additional "application hardening" you get from stored procedures is kind of like nuke-proofing a mailbox... You can harden the innermost (database) layer of an application to infinity, but if anyone that wants to can walk up and put things inside the source code then the hardening is irrelevant to protecting the data.

    As long as MS-Access is your platform and you haven't hardened it against attack, then the database is the least of your worries!

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2014
    Thanks, I will repost in MS Access forum (or can I move it somehow?). I did look at developing a web application instead of MS Access but it appeared to take > 4 weeks to do something that took around half an hour in access.. Honestly I wonder sometimes if we are actually moving forward with technology - how can a piece of software developed >15 years ago be more rapid an application development tool than what is available today. Granted you're not getting a full web application accessible by anyone BUT I don't know how things can be so much more painful than something developed all those years ago. Anyway rant over.

    Many thanks for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts