none
Front end to SQL server

    Question

  • Access is some times accused of not being "a real database" - often by people who has no other knowledge but are just "concerned"...

    I quite frankly dont know about the robustness of Access - other than I have developed in Access for some 15 years and have never experienced serious problems if the general design is good. This experience covers data logging applications with big data amounts and multi-user applications with a lot of simultaneous data access.

    BUT - to eliminate this issue of not using a "real database" I would like to consider SQL Server 2008 - as that would be a generaly acceptable solution among the critics.

    Then my question is: What is a good tool for developing a frontend for a SQL Server 2008 backend?

    Can I do it in Access...? If so - how do I communicate to the tables? Can I just link to the tables, as if they were Access tables - and then do my frontend as I am used to?

    Input is appreciated.

    Thursday, August 18, 2011 1:40 PM

Answers

  • We use Access as a front-end application for SQL Server databases all
    the time.  It is a very good rapid development environment, while the
    data is safely stored in SQL Server.
     
    I've written a PowerPoint presentation on techniques for using Access
    as a client-server front-end to SQL Server databases.  It's called
    "Best of Both Worlds" at our free J Street Downloads Page:
     
     
    It includes some thoughts on when to use SQL Server, performance and
    security considerations, concurrency approaches, and techniques to
    help everything run smoothly.
     
    Cheers,
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     

    Armen Stein, Access MVP J Street Technology www.JStreetTech.com
    • Proposed as answer by Macy Dong Friday, August 26, 2011 8:58 AM
    • Marked as answer by Macy Dong Monday, August 29, 2011 1:16 AM
    Monday, August 22, 2011 6:08 PM
  • Unfortunately there are certain ways of making Access blow up, whilst following excellent design principles. Many of Access's issues can stay latent for years before finally deciding to scrub all of your data, or damage your database so badly you cannot get access to any of your data again (and you then have to go back to your most recent backup - you cannot "import" or "export" your data or do anything else with it when it's corrupted so badly). One of the fundamental problems (from my perspective) is that Access does not operate like most Windows applications, with respect to disc operations. You don't "Save" your Access database all the time, since your records are automatically "saved" when you navigate between them. The design of Access's disc filing system functionality appears to break some of the design assumptions of certain computer hardware and software (try opening an Access database across a "mapped network drive", then put your computer's OS to "sleep", and then try waking up your computer and see what sorts of havoc that sometimes causes. I bet that would happen very quickly in a multi-user environment with lots of simultaneous data access.) Unfortunately, not all of Access' problems are well documented. I've seen Access explode plenty of times, with no reason at all (and no error messages - it just dies), when supplied with an SQL query it doesn't like. (If you don't use certain types of SQL queries with certain connection configurations that are considered perfectly valid theoretically, this may never happen to you - you can be lucky that way). But one day, you could come across an awful blocker issue where Access just dies, BOOM, and you cannot figure out why. (After weeks of strugglingm, I traced one problem like this to an SQL query, made some trivial modifications to the query by inserting some extra brackets that theoretically should have made no difference, and then everything worked fine.)

    Access is not a mature implementation of SQL standards. Once you start doing advanced SQL queries, you can run into trouble. There's nothing wrong with simple SQL queries, for most applications (such as data-logging etc.) There is probably nothing wrong with your design. You've probably only used basic functions (in which case, you might be okay for now). But if anyone ever takes your application and starts trying to do something more advanced with your database, bolting extra "advanced" functions onto it; they can quickly run into trouble like that I've experienced.

    MS Access is simply not designed to be a robust place to store large quantities of data (at least not, if you want to be reasonably certain it won't be corrupted by cosmic rays, moving magnets, deteriorating components or whatever.) Its data integrity checking and disc storage mechanisms are simply not robust enough for that. Access naively trusts the Windows/ NTFS filing system to take care of that. And fundamentally, that's not going to happen because NTFS is no longer fit-for-purpose for the amounts of data that are being stored these days. Hard drive design has come a long way since NTFS was first introduced. Hard drives have got bigger in storage capacity at a much faster rate than they have got more reliable. And databases have correspondingly increased in size... So if you're dealing with big databases, then BE WARNED:

    Access does not have very graceful methods of recovering from data corruption. It's pretty poor at this, and sometimes just awful. BOOM - data gone. Goodbye. No chance of recovery. I've faced this situation many times, even though I've been extremely careful to choose very reliable hardware and install only reliable software alongside MS Office. I've only been saved by backups (with the loss of up to a week's work on each occasion). SQL Server is MUCH more robust - especially if you go for the Enterprise version these days. But even the Express version is far more robust than Access, in just about every way.

    There's no question; I'd go for SQL Server 2008 R2.

    "Then my question is: What is a good tool for developing a frontend for a SQL Server 2008 backend?"

    MS Access 2010 is fortunately an excellent tool for developing a front-end for a SQL Server 2008 back-end. The transition is much easier than most people think (particularly if anyone on your team understands the basics of SQL Server). Install SQL Server Management Studio with MS SQL Server Express, and your job is easier. (2008 is much easier to manage than 2005, and has more sensible defaults for connections and security - so your job has recently gotten a LOT easier thanks to Microsoft's hard work here.)

    If you don't care about security, you can just use a regular ODBC link table. If you want to enhance performance and security, you should consider setting up server-enforced encryption across your ODBC links, requiring a password, and re-establishing your ODBC links every time your front-end starts up. Passthrough queries are an excellent way to improve performance in the case of complex queries with lots of results, where only a few of the results are actually used by the client machines.

    Windows has a built-in ODBC "Data Sources" control panel widget. But it's best to set up your connections in VBA code. Try searching this forum for advice about that, and you should find some good articles. (Precise method may depend on how you plan to use the data within Access.)


    Matthew Slyman M.A. (Camb.)
    • Proposed as answer by Macy Dong Friday, August 26, 2011 8:59 AM
    • Marked as answer by Macy Dong Monday, August 29, 2011 1:16 AM
    Thursday, August 18, 2011 2:01 PM

All replies

  • Unfortunately there are certain ways of making Access blow up, whilst following excellent design principles. Many of Access's issues can stay latent for years before finally deciding to scrub all of your data, or damage your database so badly you cannot get access to any of your data again (and you then have to go back to your most recent backup - you cannot "import" or "export" your data or do anything else with it when it's corrupted so badly). One of the fundamental problems (from my perspective) is that Access does not operate like most Windows applications, with respect to disc operations. You don't "Save" your Access database all the time, since your records are automatically "saved" when you navigate between them. The design of Access's disc filing system functionality appears to break some of the design assumptions of certain computer hardware and software (try opening an Access database across a "mapped network drive", then put your computer's OS to "sleep", and then try waking up your computer and see what sorts of havoc that sometimes causes. I bet that would happen very quickly in a multi-user environment with lots of simultaneous data access.) Unfortunately, not all of Access' problems are well documented. I've seen Access explode plenty of times, with no reason at all (and no error messages - it just dies), when supplied with an SQL query it doesn't like. (If you don't use certain types of SQL queries with certain connection configurations that are considered perfectly valid theoretically, this may never happen to you - you can be lucky that way). But one day, you could come across an awful blocker issue where Access just dies, BOOM, and you cannot figure out why. (After weeks of strugglingm, I traced one problem like this to an SQL query, made some trivial modifications to the query by inserting some extra brackets that theoretically should have made no difference, and then everything worked fine.)

    Access is not a mature implementation of SQL standards. Once you start doing advanced SQL queries, you can run into trouble. There's nothing wrong with simple SQL queries, for most applications (such as data-logging etc.) There is probably nothing wrong with your design. You've probably only used basic functions (in which case, you might be okay for now). But if anyone ever takes your application and starts trying to do something more advanced with your database, bolting extra "advanced" functions onto it; they can quickly run into trouble like that I've experienced.

    MS Access is simply not designed to be a robust place to store large quantities of data (at least not, if you want to be reasonably certain it won't be corrupted by cosmic rays, moving magnets, deteriorating components or whatever.) Its data integrity checking and disc storage mechanisms are simply not robust enough for that. Access naively trusts the Windows/ NTFS filing system to take care of that. And fundamentally, that's not going to happen because NTFS is no longer fit-for-purpose for the amounts of data that are being stored these days. Hard drive design has come a long way since NTFS was first introduced. Hard drives have got bigger in storage capacity at a much faster rate than they have got more reliable. And databases have correspondingly increased in size... So if you're dealing with big databases, then BE WARNED:

    Access does not have very graceful methods of recovering from data corruption. It's pretty poor at this, and sometimes just awful. BOOM - data gone. Goodbye. No chance of recovery. I've faced this situation many times, even though I've been extremely careful to choose very reliable hardware and install only reliable software alongside MS Office. I've only been saved by backups (with the loss of up to a week's work on each occasion). SQL Server is MUCH more robust - especially if you go for the Enterprise version these days. But even the Express version is far more robust than Access, in just about every way.

    There's no question; I'd go for SQL Server 2008 R2.

    "Then my question is: What is a good tool for developing a frontend for a SQL Server 2008 backend?"

    MS Access 2010 is fortunately an excellent tool for developing a front-end for a SQL Server 2008 back-end. The transition is much easier than most people think (particularly if anyone on your team understands the basics of SQL Server). Install SQL Server Management Studio with MS SQL Server Express, and your job is easier. (2008 is much easier to manage than 2005, and has more sensible defaults for connections and security - so your job has recently gotten a LOT easier thanks to Microsoft's hard work here.)

    If you don't care about security, you can just use a regular ODBC link table. If you want to enhance performance and security, you should consider setting up server-enforced encryption across your ODBC links, requiring a password, and re-establishing your ODBC links every time your front-end starts up. Passthrough queries are an excellent way to improve performance in the case of complex queries with lots of results, where only a few of the results are actually used by the client machines.

    Windows has a built-in ODBC "Data Sources" control panel widget. But it's best to set up your connections in VBA code. Try searching this forum for advice about that, and you should find some good articles. (Precise method may depend on how you plan to use the data within Access.)


    Matthew Slyman M.A. (Camb.)
    • Proposed as answer by Macy Dong Friday, August 26, 2011 8:59 AM
    • Marked as answer by Macy Dong Monday, August 29, 2011 1:16 AM
    Thursday, August 18, 2011 2:01 PM
  • If your app is simple, or only moderately complex, then Access can be a good front end.

    My app is quite complex and was converted from an Access 2000 FE/BE, to Access 2007 FE/SQL Server 2008 R2 BE. There was a lot of coding and reports that could be reused, so keeping the front end in Access made sense.

    However, if I were building it from scratch, I would not even consider Access. In theory, it should be fine, but all of the bugs in the later versions of Access, plus bugs & quirks of Access/SQL Server communication/data transfer, take it out of the running, for a complex app (IMHO).

    Thursday, August 18, 2011 5:41 PM
  • We use Access as a front-end application for SQL Server databases all
    the time.  It is a very good rapid development environment, while the
    data is safely stored in SQL Server.
     
    I've written a PowerPoint presentation on techniques for using Access
    as a client-server front-end to SQL Server databases.  It's called
    "Best of Both Worlds" at our free J Street Downloads Page:
     
     
    It includes some thoughts on when to use SQL Server, performance and
    security considerations, concurrency approaches, and techniques to
    help everything run smoothly.
     
    Cheers,
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     

    Armen Stein, Access MVP J Street Technology www.JStreetTech.com
    • Proposed as answer by Macy Dong Friday, August 26, 2011 8:58 AM
    • Marked as answer by Macy Dong Monday, August 29, 2011 1:16 AM
    Monday, August 22, 2011 6:08 PM
  • So let me see if I understand you.  Access is not mature enough or robust enough for a front-end to SQL database.  Yet after all of your rant as to blow-ups and loss of data, recommend using MSAccess 2010 as the front-end.  Apparently I missed something in translation.

    Dan

    Tuesday, October 15, 2013 1:26 AM
  • I find the better analogy between Access and SQL Server to be that of a pick up truck versus an 18 wheeler.

    Its really not about the technology.  A pick up truck's technology is not bad.

    Its about matching the appropriate tool to the task required.  A dozen crates across town is a pick up truck while a dozen tons across country calls for an 18 wheeler.

    PCs are now so powerful and cheap with so much RAM that alot can be done with Access but you surely can't run an airline reservation system on it.....  Most businesses are frugal.  As a rule of thumb: Unless you already have the sunk cost of SQL Server licenses and skills in house to call on - if it can be done with Access, do it with Access....

    Tuesday, October 15, 2013 2:26 PM
  • I really hate to see disimformation spread as Matthew is doing here.  Were Access as unreliable as he claims, thousands of us using it would be in serious trouble.  I have found (in some 35 years in this business) that the vast majority of problems, regardless of platform, are caused by incorrect usage of the tool. Most recently I was working on a POS application with over 1600 installs and the average number of users per install would be about 4.  We had a lot of BE corruption issues.  Every time, it was because of things like a user setting up a wireless network connection, bad routers, and in one case, the router was sitting on top of the key making machine.

    If Access were so bad, it would have gone the way of DBIV, QuickSilver, and some other "me too" desktop database apps.

    I do agree with Armin's anthology.  Use the appropriate tool and use it correctly.

    Tuesday, October 15, 2013 4:47 PM