Performance Tuning Tips for Using Microsoft Access and SQL Server Together

If you are interested in the fastest performance, don't use Access as a front-end to a SQL Server database. While Access is relatively easy to learn and fast to develop in, its performance is poor when compared to other front-end options. But if you like to develop in Access, or don't have any choice, then follow the tips on this page to help boost your application's performance. [6.5, 7.0, 2000, 2005] Updated 2-20-2006

*****

When you write a query in Access that needs to run against a SQL Server database, Access will locally evaluate the query, looking for any clauses or expressions that cannot be run on SQL Server, and those that can. It will do its best to run as much of the query, if not the entire query, on SQL Server rather than locally. This is because SQL Server can run the queries much faster than Access can. If Access cannot run the entire query on SQL Server, it will run as much as possible on it, and when it retrieves the results set from SQL Server, it will then complete the query locally.

In order to speed up Access queries, you should try to write Access queries that will run entirely, or as much as possible, on SQL Server, instead of locally on Access. While the list is too long to include here, some of the query clauses and expressions that can run on Access but not on SQL Server, and ones you should try to avoid, include domain aggregate function, financial functions, and user-defined functions. [6.5, 7.0, 2000, 2005] Updated 2-20-2006

*****

When you design forms that include data from a SQL Server, try to avoid downloading data that the user doesn't immediately need. This will reduce network traffic, reduce the load on the server, and speed overall performance. Some ways to reduce the amount of the data you download to a form include:

• Try to avoid opening large Recordset objects and then navigating through them using one of the Find methods. Instead, use a filter or query to limit the size of the recordset in the first place.
• Only retrieve fields from records that you actually need on the form.
• Avoid using bound controls on your form. Each bound control creates a separate query that must be sent to and run on SQL Server.
• If the user doesn't need to view all the data from a record in a SQL Server database at the same time, use two forms. The first form will include the most important data and it will be retrieved from SQL Server based on a query to SELECT only the data that needs to be on this first form. If the user needs the rest of the data, then the user can click on a button to bring up the second form. The data for the second form is only requested if the user clicks on the button for the second form. This way, only the data that is needed for the current form is ever requested from SQL Server.
• Don't display Memo or OLE object fields on a form unless the user specifically requests this information by clicking on a button. The advantage of this technique is that Access does not retrieve Memo or OLE object fields from SQL Server until the fields themselves are displayed on the screen.

[6.5, 7.0, 2000, 2005] Updated 2-20-2006

*****

When displaying SQL Server data on a form, Access allows you to create either a dynaset or a snapshot Recordset object. If you don't need to update the data displayed on the form, and if the Recordset has less than 500 records, then the snapshot Recordset will be faster than the dynaset Recordset. But if the Recordset is over 500 records, or if the records include Memo or OLE Object fields, then the dynaset Recordset object is faster. [6.5, 7.0, 2000, 2005] Updated 2-20-2006

*****

If possible, avoid using subforms in your Access forms. Subforms require at least two queries to be issued to SQL Server, and require more overhead. Instead, use a query to join the tables you need and display the results in the form. This only requires one query to be sent to SQL Server and has less overhead. Most columns from multiple-table tables can have data inserted or updated into them, so subforms can often be avoided. [6.5, 7.0, 2000, 2005] Updated 2-20-2006

*****

One way to increase performance of DELETEs or UPDATEs made by your Access front-end to a SQL Server backend is to ensure that the table on SQL Server has a timestamp column. If a table does have a timestamp column, then when Access DELETEs or UPDATEs a row, Access will automatically check the timestamp column to see if it has changed during the time that the transaction begun and the current time that the deletion or update is being made. If the timestamp column has changed, then the DELETE or UPDATE will be aborted. If a timestamp column does not exist in the table, then Access will have to compare all the field values when the transaction begun to their current value to determine if there were any changes. This is much slower for Access to perform than checking the timestamp column. [6.5, 7.0, 2000, 2005] Updated 2-20-2006

*****

To help improve scalability and performance of INSERTs, UPDATEs, and DELETEs, try to include related INSERTs, UPDATEs, and DELETEs together in formal transactions. For example, if a particular transaction includes multiple UPDATEs, then these will all be submitted to SQL Server as one single batch, instead of many separate batches. This reduces network traffic and server overhead. But like any transactions, they should always be made as short as possible to minimize locking on the SQL Server tables. [6.5, 7.0, 2000, 2005] Updated 2-20-2006