Article ID: 128808 - View products that this article applies to.
This article was previously published under Q128808
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article lists tips to help you optimize performance when you use Open Database Connectivity (ODBC) data sources with Microsoft Access.
This article assumes that you are familiar with client/server environments and architectures.
Methods of Accessing DataTo access server data with a client/server application, you can use one or more of the following methods:
Attached Tables and ViewsThe most efficient way to handle server data is to attach SQL tables and views from the server. Microsoft Access stores field and index information for attached tables. This improves performance when you open the tables. Note that you must re-attach remote tables if you make changes to fields or indexes on the server.
Using SQL Pass-Through QueriesIn many applications, you can use both Microsoft Access queries based on remote tables and SQL pass-through queries. Pass-through queries offer the following advantages:
Request Less Data from the ServerRequesting data from the server costs processing time. To optimize performance, request only those records and fields that you need.
Reduce the number of bound controls, such as text boxes, combo boxes, list boxes, and subforms that you use. When a form is opened, each of these controls requires a query to be sent to the server.
If the attached ODBC tables have Memo or OLE object fields, you can set the Visible property of the fields to No and add a toggle button to set the Visible property to Yes so that users can choose to view it.
Some attached tables (for example, a table containing the names and abbreviations of the 50 states) do not change frequently. You can speed up form loading and reduce server load by using a local copy of these tables. You can also provide a way to update the local copy with more recent data from the server.
Optimizing QueriesThe principal method for optimizing queries (after adding appropriate indexes on the server) is ensuring that processing takes place on the server. You should avoid functionality that is not supported by the server, such as functions specific to Microsoft Access or user-defined functions. For detailed information about what must be processed locally, see the Microsoft Jet Database Engine ODBC Connectivity white paper (for more information about this white paper, see the "Microsoft Jet Database Engine ODBC Connectivity White Paper" section later in this article).
To see the Select statement that is being sent to the server, you can set the TraceSQLMode setting. In Microsoft Access 7.0 and 97, you have to edit the Registry to add the option for TraceSQLMode.
For more information on editing the Registry to change ODBC settings, please see the following article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/139044/EN-US/ )ACC95: How to Add Former MSACC20.INI ODBC Section to Registry
For more information about the TraceSQLMode setting in Microsoft Access 97 search for "TraceSQLMode" using the Find option of the Microsoft Access 97 Help Topics.
For more information about the TraceSQLMode setting in Microsoft Access 2.0, search for "MSACC20.INI" then "Customizing MSACC20.INI Settings" using the Microsoft Access Help Menu.
TryJetAuth SettingThe TryJetAuth setting controls whether Microsoft Access first attempts to log on to your server using the Microsoft Access login ID and password. (By default, the Microsoft Access login ID is "admin" and the password is blank.) If this fails, Microsoft Access prompts you for a login ID and password. Unless you have set up Microsoft Access to use the same login ID and password as your ODBC server, add the line "TryJetAuth=0" to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5\Engines \ODBC key of the Windows Registry. This prevents Microsoft Access from attempting to log on with the wrong ID.
NOTE: In Microsoft Access 2.0, add the line "TryJetAuth=0" to the MSACC20.INI file.
TimestampsOn servers that support them (such as Microsoft SQL Server), timestamp fields make updating records more efficient. Timestamp fields are maintained by the server and are updated every time the record is updated. If you have a timestamp field, Microsoft Access needs to check only the unique index and the timestamp field to see whether the record has changed since it was last retrieved from the server. Otherwise, Microsoft Access must check all the fields in the record. If you add a timestamp field to an attached table, re-attach the table in order to inform Microsoft Access of the new field.
TransactionsUsing transactions when you update or insert records on attached tables in Access Basic can improve performance. Transactions enable the Jet database engine to accumulate multiple updates and write them as a single batch. With Microsoft SQL Server, keep transactions short because they generate locks that prevent other users from reading data affected by the current transaction. Although you can nest transactions using Visual Basic, most servers do not support nested transactions. Microsoft Access sends only the first-level transaction to the server.
Optimizing Recordsets and CodeYou can store all or part of the data contained in Recordset objects of the Dynaset type in local memory by setting the CacheSize and CacheStart properties.
The cache size can be between 5 and 1200 records. If the size of the cache exceeds available memory, the excess records spill into a temporary disk file. Applying the FillCache method fills the cache with server data. To recover the cache memory, set the CacheSize property to zero.
When you use Recordset variables, use only the functionality that you need. For example, you can use the DB_APPENDONLY option on the OpenRecordset method if you only need to add new records to a recordset. If you do not need editing or updating ability, base your recordsets on SQL pass-through queries. The fastest method to insert and update records is to use SQL pass-through queries in code.
MSysConf TableYou can create a table named MSysConf on your server to control background population (the rate at which Microsoft Access reads records from the server during idle time). You can use this table to set the number of rows of data that are retrieved at one time and the number of seconds of delay between each retrieval. If you experience excessive read-locking on the server, you can adjust the table settings to increase background population speed. If too much network traffic is generated by background population, you can adjust the settings to slow it down.
Microsoft Jet Database Engine ODBC Connectivity White PaperAn important source for additional information is the Jet Database Engine ODBC Connectivity white paper. This document discusses the Microsoft Jet database engine version 3.0 and how it uses ODBC.
For more information on optimizing client/server applications, see Microsoft Access "Building Applications," Chapter 19, "Developing Client/Server Applications," pages 523-556
For information on how to obtain the Jet Database Engine ODBC Connectivity white paper, please see the following article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/128385/EN-US/ )ACC95: Jet & ODBC White Paper Available in Download Center
Article ID: 128808 - Last Review: January 19, 2007 - Revision: 2.2