Peeking Under the Hood

I have been using SQL Server as backend data storage for all of my new projects for the past several years.  I still maintain many older applications that use Microsoft Access database files as backend data storage.  

I've always been impressed with how well Access is able to hide the complexity of its interaction with linked SQL Server tables.  In many cases, you can simply migrate your tables from an Access backend to SQL Server, relink the tables, and everything just works.

But, as impressive as the Access integration with SQL Server is, it is far from being a watertight abstraction.  So, to deal with the inevitable leaks in the abstraction, it's important to have an understanding of what's actually going on under the hood.

The best explanation I've ever read is an article by Andy Baron in 2006, Optimizing Microsoft Office Access Applications Linked to SQL Server.  It's a long read, but it's chock full of nuggets of information.  It's the kind of article that you read once, learn a few things, but a bunch of stuff just goes over your head.  You take a few months to apply the things you learned, then you go back and read it again.  The second time through, a few things that went over your head the first time make sense.  You'll understand most of it by your fourth or fifth time through it, but each time after that you'll read some detail that you had completely forgotten about.

Here are a few of my favorite bits to whet your appetite:

Diagnostic Tools

To enable tracing of all ODBC commands from the Jet database engine (TraceSQLMode = 1)...
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\ODBC

Understanding Dynasets

Form data isn't fetched the way you might expect.  By default, Access pulls it over 10 rows at a time, even if your form is bound directly to a table with hundreds of records.

The statement that fetches 10 rows of data is repeated as many times as necessary to fill the current screen and to provide some room for scrolling in either direction.

Adjusting Dynaset Behavior

Would you ever have guessed that the name of your indexes could be meaningful in how Access pulls data from SQL Server?  It's true.  This bit me hard years ago.  It's why all my primary keys in SQL Server are prefixed with aaaPK_.

If all your unique indexes are nonclustered, Office Access uses the one that happens to be listed first alphabetically, which may not be the most efficient choice.

Using Pass-Through Queries to Optimize Bulk Updates

You know those set-based operations, like UPDATE, that make relational databases so efficient at updating large amounts of data at a time?  Yeah, Access is too much of a control freak about its linked SQL Server tables to take advantage of a feature like that.

When you perform bulk updates to linked data by executing Office Access queries, the rows are modified one at a time. The Office Access database engine executes a separate statement for each row being affected, instead of using more efficient set-based operations that affect many rows at once.

Supporting Concurrency Checks

Do your users ever get those messages that the "data has been changed" even though the record they are editing had not been accessed by any other users?  You might be able to fix those errors just by adding a rowversion column to your SQL Server tables.  The best part is that Access uses the column even if you don't update your queries to specifically include it.  And, there's almost no downside to doing it.  Almost every SQL Server table I create these days includes the following field definition: , odbc_id rowversion   --improve ODBC linked table concurrency.

Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table.

Conclusion

Many developers mourned the loss of  Access data projects (.ADP), but much of their increased speed and scalability can be realized using the techniques in the above article.

By understanding how Office Access interacts with SQL Server, and by taking steps to move as much query processing to the server as possible, developers can take advantage of SQL Server features while continuing to create rich applications quickly with Office Access.

Image by Bruno /Germany from Pixabay