SQL Server Developmentbook

Access to SQL Server Efficency

I've run into many of my clients that have been using Microsoft Access databases for many in-house built solutions.  Ultimately they run into problems with performance.  One of my first suggestions is to use Microsoft SQL Server as their database and keep their Access front-end code with all of their reports.  I have been met with one huge objection, the cost of purchasing the SQL Server license.  Well now Microsoft has SQL Server Express, basically a free version of SQL Server.

Once link to the SQL Server database, move as many of your queries as possible to SQL Server by creatnig views.  There will be a noticable improvement in performance.  There may still be some problem views/queries left over that just don't perform well.  There can be views of views of views where some of the inner level views just don't carry enough of the selection criteria to narrow down to a manageable number of records.   One of my clients did this and they still had a report that took over 30 minutes to run.  After digging around for a couple of hours, I was able to find the culprit.  There was a view that was performing a group on all of the accounting records and the high level query was selecting maybe a couple of hundred records.  Since it was not a parameter driven view, I was able to apply the same narrowing criteria at the lower level view.  This returned a recordset to the calling report in just over a minute.

There may be some cases where he queries are parameter driven.  At this point we need to consider moving to stored procedures and linking those to reports.  While a Microsoft Access .mdb file does not directly connect to Stored Procedures, a Microsoft Access Project file (.adp suffix) does in fact connect to an entire SQL Server database.  This gives you complete access to attach reports to stored procedures.  More about this next post.