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

  • Article

By Mary Chipman

In Part 2 I'll continue the discussion about how you can "tread lightly" on your SQL Server by making your Access front end an efficient and economical data consumer. I'll focus on techniques you can use to keep your application running efficiently, freeing up network and server resources in the process.

Moving data processing to the server

Access evolved over the years as a single tier application, and as such, became increasingly efficient at using desktop resources efficiently. The increase in computing power on the desktop meant that often an entire Access database can fit into memory. However, in a two-tier, client-server application, it is usually more efficient to move all processing to the server, implementing business and data rules in stored procedures and to a lesser extent, views and functions.

In addition to making the Access client application more efficient, moving data processing to the server provides "one-stop shopping" that allows multiple applications to connect to the database without having to implement separate data rules in each application. Most importantly, moving data processing to the server allows you to implement more robust security.

If you are new to SQL Server, then one of the steepest learning curves involves SQL Server security. If you are familiar with Access user-level security, then you are already aware that it uses a default mode of "off", which means anyone who can access the file share that the database is stored on can work with the data. SQL Server is just the opposite, its default mode is on, which means you must explicitly grant permissions for users to work with the data. While I was at Microsoft, I wrote a series of topics for the ADO.NET documentation explaining the basics, which are not specific to ADO.NET. See Overview of SQL Server Security (ADO.NET) https://msdn.microsoft.com/en-us/library/bb669078.aspx. Among the topics covered are authentication, server and database roles, user-schema separation, authorization and permissions. You will also find links to the relevant topics in SQL Server Books Online when you need to drill down for more detailed information.

Views

SQL Server views are saved SELECT queries written in Transact-SQL, and they are always executed on the server. You can link to a view the same way you link to a table. However, they differ substantially from Access saved queries in that they do not support parameters, the ORDER BY clause, or updating multiple tables. Since views are written in Transact-SQL, you will need to understand the differences between Transact-SQL and Access SQL when it comes to wildcard characters, quotation marks and built-in functions. The Optimizing paper contains workarounds for some of these limitations, and you can learn more about how to create views in the topic Views https://msdn.microsoft.com/en-us/library/ms190706.aspx in SQL Server Books Online.

Stored procedures and triggers

Many SQL Server developers and DBAs prefer to create a data access layer using stored procedures. This allows you to encapsulate all data access, which simplifies troubleshooting and ensures more robust security. See CREATE PROCEDURE (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms187926.aspx in SQL Server Books Online and Managing Permissions with Stored Procedures in SQL Server (ADO.NET) https://msdn.microsoft.com/en-us/library/bb669058.aspx, which contains links to additional topics involving writing robust stored procedures. Some topics will not apply as Access does not support writing ADO.NET code, but the same general principles still apply as most ADO.NET techniques have an ADO/Visual Basic equivalent.

You can create triggers that fire on SQL Server events, such as inserts, updates and deletes, but you are better off avoiding triggers in your Access-SQL Server application unless you have a good reason for using them. An Access query is capable of returning only one result set, and when there are triggers involved, multiple data operations are taking place under the hood, with unpredictable results. This makes troubleshooting problems with queries more complex because triggers are "hidden" unless you use a tool such as Profiler. They also present potential elevation-of-privilege vulnerabilities. For more information, see CREATE TRIGGER(Transact-SQL) https://msdn.microsoft.com/en-us/library/ms189799.aspx in SQL Server Books Online.

Optimizing forms using client-side caching and query-by-form techniques

If you have forms based on Access queries, make sure that you aren't including unnecessary joins. Fetch only needed data, and be aware that complex forms, such as tabbed forms, load all of the data when they open, whether the user clicks on the tab or not. You can avoid this unnecessary performance hit by only loading data on demand using form and control event procedures.

If you have static data that rarely changes, you can populate local Jet/ACE tables with data by importing it when your application loads, or on demand. You can then use the local data to populate combo and list boxes.

  1. Begin by creating a pass-through query in Access, setting its Returns Records property to Yes.
  2. Type the Transact-SQL statement you want to use (or execute a stored procedure) and save the pass-through query as CityList:

SELECT DISTINCT City From dbo.Customers

ORDER BY City

  1. Next, create an Append query that selects from the pass-through query and inserts data into the local table. Save the Append query as qryAppendCity.

INSERT INTO CitiesLocal ( City )

SELECT sptCityList.City

FROM sptCityList;

  1. You can then base combo boxes and list boxes on the local table.
  2. When you need to refresh the data in the local table, you can do so easily by writing the following DAO/VBA code:

Dim db As DAO.Database

Set db = CurrentDb

' Delete existing data

db.Execute "DELETE * FROM CitiesLocal"

' Run the append query

db.Execute "qryAppendCity"

Although you can execute stored procedures from Access pass-through queries and through code, the result sets returned are always read-only, so they don't work for bound forms that update data. You then need to use unbound form techniques, which require more programming.

Query-by-form techniques involve opening forms with no bound data sources for the form itself or any of the controls on the form, and forcing the users to select from a series of combo boxes that restrict the amount of data being fetched by specifying a WHERE clause. The principle here is that users can only edit one record at a time, so it doesn't make sense to fetch potentially thousands or millions of rows for them to scroll through in order to locate that one record. You can use pass-through queries, which return read-only result sets, as the basis of scrollable forms and reports. You can use one of the Access form or control events to load a selected record into a new form for editing.

The following code fragment demonstrates how you can modify a pass-through query in code to execute a parameterized stored procedure. Note that the parameter value must be explicitly specified. You cannot refer to controls on a form or to VBA functions when passing parameter values to SQL Server.

 Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs(execSproc)
' Set the connection string
qdf.Connect = strConnectionString
qdf.ReturnsRecords = True
qdf.SQL = "EXEC procGetCustomersByRegion 'NY'"

You can find the correct syntax for supplying SQL Server connection strings at run time here: https://www.connectionstrings.com/sql-server-2008#p3. This eliminates the problem of having to configure and support multiple DSNs for your users.

Optimizing reports using pass-through queries

You can use pass-through queries as the basis for reports as well as forms. Doing so forces the query to execute on the server, not the client workstation. This becomes important when you have large data sets, or need to aggregate data and perform complex calculations. If you use linked tables as the basis for reports, Access returns the data to the client for processing, which can slow processing and consume excess server resources.

If you are manipulating pass-through queries from code, set the .SQL property of the pass-through query to "WHERE 1=0" when the report closes. The reason for this is that Access forms and reports fetch column definitions and metadata before the Open event, which can cause a delay if there is already a complex WHERE clause specified in the pass-through query. You can then specify a valid WHERE clause in the Open event of the report.

Using SQL Server TVPs from Access

Table-valued parameters were introduced in SQL Server 2008. They allow you to pass multiple rows to SQL Server so that they can be processed as a batch, with all being either committed at once, or rolled back as a single unit of work. For example, you can use TVPs to submit an order with multiple order details line items, and have it executed as a single unit of work inside of an explicit transaction, instead of piecemeal with a separate update statement for each line item.

TVPs are explicitly supported for the ADO.NET SqlClient data provider, but you can also use them from Access if you are willing to jump through a few hoops. The downside is that there is no design-time syntax checking or run time error handling available from Access. For more information, see Using SQL Server 2008 Table-valued Parameters in Access 2007 https://msdn.microsoft.com/en-us/library/dd721896.aspx. You can also download a complete demo application from MSDN Code Gallery. See TwitterTracker Demo https://code.msdn.microsoft.com/TwitterTrackerDemo.

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/.