Access to SQL Server Migration: Access Solutions using SQL Server, Part 1

By Mary Chipman

In Part 1 and Part 2 I’ll discuss your options for continuing to use Access as a front-end client once you have migrated the data to SQL Server. Some people come to SQL Server migration as Access developers who are new to SQL Server, whereas others may be IT professionals who know little about Access, so I will attempt to bridge any gaps with useful information for everyone. The SSMA Migration Assistant for Access (SSMA) makes it easy to link converted SQL Server tables and queries to the Access database you started from. For applications of any complexity, migrating the data is by far the easiest step. If your existing Access application suffered from intractable problems when the tables were in native Jet/ACE format, and you have done nothing to fix or at least understand those problems, then you are most likely going to have even bigger problems with SQL Server.

The Access client application has often been dismissed as a "toy" because of its ease of use, but when understood and used appropriately with SQL Server on the back end, those very features can help you quickly deliver cost-effective results with a low TCO. Access is not a suitable client for Web or n-tier applications that require a separate data access layer, although it can be used in conjunction with other clients, such as ASP.NET. There are always tradeoffs involved between ease of development, efficiency and security when using Access in a 2-tier, client-server scenario. You will need to determine which of the following techniques are best suited to your needs.

Linking tables vs. ADPs (Access Projects)

Historically, one of the virtues of Access is that the Jet/ACE database engine can work with its own native tables in addition to tables it links to using Open Database Connectivity (ODBC). ODBC provides connectivity to all the common relational database servers, including SQL Server, Sybase, Oracle, IBM DB2, and Informix. When SSMA links SQL Server tables to your Access application, you have the ability to write queries against those tables using the syntax and designers you may already be familiar with. You can also use pass-through queries to submit commands directly to the server, which makes them highly efficient because they are not parsed until they reach the server. Pass-through queries are written in the dialect of the server, in this case Transact-SQL, and can return read-only result sets. Access provides no syntax checking or designer for pass-through queries. Instead, use SQL Server Management Studio to generate the correct syntax, and then copy/paste it into the .SQL pane in the Access pass-through query designer.

Access project files, also called ADPs or .adp files, were introduced to provide an alternative to using ODBC. An ADP allows an Access application to connect directly to a single SQL Server database. All data objects are stored on the server, there is no local database engine. Originally ADPs provided a designer for creating and modifying tables and other SQL Server objects. However, the designer functionality in ADPs has not kept up with SQL Server 2005 and SQL Server 2008. ADPs will continue to be supported, but Microsoft no longer recommends them. See ADP's and SQL Server from the Access team blog https://blogs.office.com/b/microsoft-access/archive/2009/01/21/adp-s-and-sql-server-2008.aspx.

When you link directly to SQL Server tables from Access, you are incurring network and server overhead that does not exist with native Access/Jet/ACE tables. As a result, techniques that worked well when all of the data was on the desktop may incur a performance penalty or increase server/network load in ways that might be undesirable if you want your application to scale or to support many concurrent users. Some of the ways you can optimize your Access/SQL Server application include the following:

• Use native Access Jet/ACE tables for client-side caching of static data.
• Base editable forms on restrictive result sets instead of linking to entire tables.
• Use pass-through queries to access stored procedures or send SELECT statements directly to the server when you only need read-only result sets.
• Optionally, investigate "unbound" techniques that don't use linked tables at all. This is probably overkill for most applications, but it is possible to create a lightweight client if you are willing to invest in the amount of coding required to handle concurrency and form state.

I'll touch lightly on some of those techniques in this post. For more information, see the Additional Resources and References section below.

SQL Server Profiler

Every database application has its own set of unique requirements, which often change and evolve over time. Techniques that work well with a small data load and few users often don't scale well into the future. If you have not worked with SQL Server before, getting up to speed with SQL Server Profiler during development will help you ensure that your Access-SQL Server application is utilizing server resources efficiently. Profiler lets you examine the conversation between Access and SQL Server by selecting and monitoring Transact-SQL statements that are being executed on the server. Profiler is also useful in production when you need to troubleshoot performance problems or bottlenecks, but it consumes a considerable amount of resources on the server, so you don't want to have it running continuously in a production environment. The following steps demonstrate how easy it is to set up a simple trace:

1. Launch Profiler and choose File, New Trace from the menu. You'll be prompted to connect to an instance SQL Server.
2. In Use the template drop-down list, choose TSQL_duration.
3. Click on the Events Selection tab, and check the Show All Events checkbox.
4. Scroll down the Events Selection list and select the Transact-SQL events you are interested in.
5. Click the Organize Columns button and move the events you are interested in monitoring towards the top of the list as shown in the following screenshot, and click OK. Note that you can also apply column filters to limit the amount of data returned.

1. Click Run. Switch to your Access application and execute some queries. The TextData column displays the Transact-SQL statements the server is processing, as shown in the following screenshot. The bottom pane shows the Transact-SQL statements, and the columnar data displays the statistics you have selected. You can also save the trace results in a file or database table.

This short demo only scratches the surface; see Introducing SQL Server Profiler https://msdn.microsoft.com/en-us/library/ms181091.aspx in SQL Server Books Online for the complete documentation.

Understanding concurrency, timestamps, dynasets and snapshots

When you open a form or a datasheet that is bound to a linked SQL Server table, you are loading a dynaset recordset, which is also known as a keyset cursor. In a dynaset, Access fetches the key values, which could be the primary key or a unique index, and the first few row of data. As the user scrolls through the data, Access uses the cached key values to fetch the remaining rows. Dynasets are explained in full in the paper Optimizing Microsoft Office Access Applications Linked to SQL Server https://msdn2.microsoft.com/en-us/library/bb188204.aspx, so I won't repeat that information here. Dynasets support a continuous two-way conversation between Access and SQL Server, which means that read locks are being held on the data as it is being fetched. A snapshot, which is also known as a firehose cursor, fetches all of the data immediately, so it can take longer to load large result sets. Snapshots do not hold any locks on the server once the data has been fetched.

If the table has a timestamp, then SQL Server uses it to manage concurrency. A timestamp (or rowversion) is a binary value that automatically changes when a row is changed, it has nothing to do with the actual time, as shown in the screenshot below.

SQL Server compares the timestamp value on the server with the timestamp value in the Dynaset in order to determine if the row has been changed since it was retrieved from the server. If you were to edit one of the Order Details rows, SQL Server would automatically generate a new, non-sequential binary value in the timestamp column. For more information, see rowversion (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms182776.aspx in SQL Server Books Online.

If you need to track users who update rows, or track when data has been changed, you can add columns to the table and use Transact-SQL functions, such as SUSER_NAME() to generate the data. For more information, see Built-in Functions (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms174318.aspx.

You can adjust dynaset behavior and optimize concurrency using the techniques discussed in the Optimizing paper. However, for optimal efficiency, you may want to consider moving as much of the data processing load as possible to the server, which is covered in Part 2.

Additional Resources and References

Optimizing Microsoft Office Access Applications Linked to SQL Server https://msdn2.microsoft.com/en-us/library/bb188204.aspx describes in detail many of the techniques that were only mentioned briefly in this blog post.

Migration considerations for Access 2007 https://technet.microsoft.com/en-us/library/cc178973(office.12).aspx primarily describes migrating from earlier versions of Access to Access 2007, but there is also useful information about linking to SQL Server.

The UtterAccess forum has a discussion group dedicated to SQL Server questions https://www.utteraccess.com/forum/Microsoft-SQL-Server-f32.html. You can post questions and get answers from Access experts who work with SQL Server on a daily basis.

The FMS Upsizing Resource Center contains links to various resources https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html.

JStreet Technology's developer downloads section https://www.jstreettech.com/cartgenie/pg_developerDownloads.asp has useful tools and presentations to help you get up to speed.

Bio

Mary Chipman is an independent consultant who has written and spoken extensively about Microsoft data platforms and technologies. She was awarded MVP status (Most Valuable Professional) from 1994 through 2004 when she joined Microsoft, working as a programmer/writer until 2010. She has authored and presented award-winning SQL Server and .NET courseware for Application Developers Training Company (AppDev.com) and spoken at industry conferences, most recently TechEd 2008 and 2009. She is co-author of the classic Microsoft Access Developer's Guide to SQL Server https://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446/.