Advanced: Requires expert coding, interoperability, and multiuser skills.
This article lists tips to help you optimize performance when you use OpenDatabase Connectivity (ODBC) data sources with Microsoft Access.
This article assumes that you are familiar with client/server environmentsand architectures.
Methods of Accessing Data
To access server data with a client/server application, you can use one ormore of the following methods:
- Attached tables and views
- SQL pass-through queries to send SQL statements directly to the server
Attached Tables and Views
The most efficient way to handle server data is to attach SQL tables andviews from the server. Microsoft Access stores field and index informationfor attached tables. This improves performance when you open the tables.Note that you must re-attach remote tables if you make changes to fields orindexes on the server.
Using SQL Pass-Through Queries
In many applications, you can use both Microsoft Access queries based onremote tables and SQL pass-through queries. Pass-through queries offer thefollowing advantages:
- Microsoft Access does not compile a pass-through query. It sends the query directly to the ODBC server, speeding up the application.
- Microsoft Access sends the pass-through query to the server the way you enter it. Therefore, more processing occurs on the server and less data is exchanged over the network.
- Forms, reports, and Microsoft Access queries can be based on pass- through queries using ODBC attached tables.
- You can use SQL Server-specific functionality, such as stored procedures and server-based intrinsic functions that have no equivalent in code.
- Update, delete, and append pass-through action queries are much faster than action queries based on attached remote tables, especially when many records are affected.
Pass-through queries have the following disadvantages:
- An SQL pass-through query always returns a snapshot that cannot be updated. A Microsoft Access query usually returns a dynaset that reflects other users' changes and can be updated.
- You type the commands directly into the SQL Pass-Through Query window with the syntax that your server requires. You cannot use the graphical query by example (QBE) grid.
- To use a parameter with a pass-through query, you must run the query in code and modify the query's SQL property. For an example of how to modify the SQL property, see "Building Applications with Microsoft Access 97," Chapter 19, "Developing Client/Server Applications," pages 550-551.
Request Less Data from the Server
Requesting data from the server costs processing time. To optimizeperformance, 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 ofthese controls requires a query to be sent to the server.
If the attached ODBC tables have Memo or OLE object fields, you can setthe Visible property of the fields to No and add a toggle button to setthe Visible property to Yes so that users can choose to view it.
Some attached tables (for example, a table containing the names andabbreviations of the 50 states) do not change frequently. You can speed upform 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 datafrom the server.
The principal method for optimizing queries (after adding appropriateindexes on the server) is ensuring that processing takes place on theserver. You should avoid functionality that is not supported by theserver, such as functions specific to Microsoft Access or user-definedfunctions. For detailed information about what must be processed locally,see the Microsoft Jet Database Engine ODBC Connectivity white paper (formore information about this white paper, see the "Microsoft Jet DatabaseEngine ODBC Connectivity White Paper" section later in this article).
To see the Select statement that is being sent to the server, you canset the TraceSQLMode setting. In Microsoft Access 7.0 and 97, you haveto 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:139044
ACC95: How to Add Former MSACC20.INI ODBC Section to Registry
For more information about the TraceSQLMode setting in Microsoft Access 97search for "TraceSQLMode" using the Find option of the Microsoft Access 97Help Topics.
For more information about the TraceSQLMode setting in Microsoft Access2.0, search for "MSACC20.INI" then "Customizing MSACC20.INI Settings" usingthe Microsoft Access Help Menu.
The TryJetAuth setting controls whether Microsoft Access first attempts tolog on to your server using the Microsoft Access login ID and password. (Bydefault, the Microsoft Access login ID is "admin" and the password isblank.) If this fails, Microsoft Access prompts you for a login ID andpassword. Unless you have set up Microsoft Access to use the same login IDand password as your ODBC server, add the line "TryJetAuth=0" to theHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5\Engines\ODBC key of the Windows Registry. This prevents Microsoft Access fromattempting to log on with the wrong ID.
NOTE: In Microsoft Access 2.0, add the line "TryJetAuth=0" to theMSACC20.INI file.
On servers that support them (such as Microsoft SQL Server), timestampfields make updating records more efficient. Timestamp fields aremaintained by the server and are updated every time the record is updated.If you have a timestamp field, Microsoft Access needs to check only theunique index and the timestamp field to see whether the record has changedsince it was last retrieved from the server. Otherwise, Microsoft Accessmust check all the fields in the record. If you add a timestamp field to anattached table, re-attach the table in order to inform Microsoft Access ofthe new field.
Using transactions when you update or insert records on attached tables inAccess Basic can improve performance. Transactions enable the Jet databaseengine to accumulate multiple updates and write them as a single batch.With Microsoft SQL Server, keep transactions short because they generatelocks that prevent other users from reading data affected by the currenttransaction. Although you can nest transactions using Visual Basic, mostservers do not support nested transactions. Microsoft Access sends onlythe first-level transaction to the server.
Optimizing Recordsets and Code
You can store all or part of the data contained in Recordset objects ofthe Dynaset type in local memory by setting the CacheSize and CacheStartproperties.
The cache size can be between 5 and 1200 records. If the size of the cacheexceeds available memory, the excess records spill into a temporary diskfile. Applying the FillCache method fills the cache with server data. Torecover 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 OpenRecordsetmethod if you only need to add new records to a recordset. If you do notneed editing or updating ability, base your recordsets on SQL pass-throughqueries. The fastest method to insert and update records is to use SQLpass-through queries in code.
You can create a table named MSysConf on your server to control backgroundpopulation (the rate at which Microsoft Access reads records from theserver during idle time). You can use this table to set the number of rowsof data that are retrieved at one time and the number of seconds of delaybetween each retrieval. If you experience excessive read-locking on theserver, you can adjust the table settings to increase background populationspeed. 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 Paper
An important source for additional information is the Jet Database EngineODBC Connectivity white paper. This document discusses the Microsoft Jetdatabase engine version 3.0 and how it uses ODBC.