By Tom Wickerath, Microsoft Office Access MVP
Ever wish you could get more power and better performance from your Access applications? Experienced Access database developers know the techniques for speeding up an application, especially a networked application with linked tables. But just being aware of the common settings (even the default ones) that slow down a Microsoft Access application will help you decide where you can optimize your application's performance.
You should always split a multi-user database into front end (FE) and back end (BE) databases. A copy of the FE database should be installed on each user's local hard drive. The BE database, which contains only the shared tables, is located on a file server. Sharing an entire database over the network has been identified by Microsoft personnel as the number one cause of database corruption, so don't do it. For more information on split databases, please see the gem tip: Split the Database.
Here's a list of steps to take and habits to acquire (or avoid) to help you speed up the performance of your Access application:\
Disable Name AutoCorrect
See the Microsoft Knowledge Base article: "Slow Performance When User Opens an Object with Name AutoCorrect Enabled":
For additional reasons why one should avoid this default option, see Access MVP Allen Browne's tip, "Failures caused by Name Auto-Correct":
Set all table Subdatasheets to [None]
See the Microsoft Knowledge Base article: "BUG: Slow performance on linked tables in Access 2002 and Office Access 2003 and Access 2007", includes code for fixing all tables:
Note: You must disable the Track Name AutoCorrect option first. Otherwise, a subdatasheet property set to [None] can easily revert back to [Auto].
Compact the database often
During compaction, pages of records and indexes are reordered in contiguous blocks for faster sequential access, and the table statistics are updated. Compacting the database allows queries to use the new table statistics to create a new optimization plan the first time the queries are run following the compact operation. Queries can become very slow if they are based on an old optimization plan that is no longer valid for the data at hand.
Design for a multi-user environment, not a single user
Many Access applications migrate from a single user application to an application that needs to be used by numerous people in the organization. The Access development environment allows -- and even encourages -- novice users to use default settings, such as Name AutoCorrect and Subdatasheets, along with techniques such as table lookup fields, that can be performance killers in multi-user applications. Pessimistic locking and page-level record locking usually won't show a noticeable impact on performance in a single user database, but in a mult-iuser environment the read/write delays will keep the users waiting, so use optimistic locking and row-level locking whenever possible.
Consider isolating Memo, OLE object and Hyperlink data types in separate tables with a 1:1 relationship to the rest of the columns in the original table, since "record-level locking is not enabled for Memo data types." See "Record-level locking" in this Microsoft KB article, about 2/3 down the page.
Dragging Recordsets across the network can also severely impact performance. An inefficient query may take seconds to execute in a single user application, but may take several minutes to execute when accessing this query with data stored on a file server.
Whenever a network is involved, picture a sprint runner who is forced to breath through a straw. The goal should be to reduce the amount of data that must be transferred over the network. For example, don't open forms bound to huge recordsets. Select only the fields needed and use criteria in queries to narrow down exactly which records are needed. Make sure to index fields that are used to specify sorts or criteria. Similar to this tip, bound combo boxes and list boxes should not include hundreds of records. A modification can be made in the case that there are many hundreds of records (http://msdn2.microsoft.com/en-us/library/aa188218(office.10).aspx), which is especially important if the database is split with the back end on a file server.
Verify folder privileges
Each user must have RWCD privileges for the shared folder, in order to share a database for simultaneous use. The following Microsoft Knowledge Base article provides more information on this topic:
"Introduction to .ldb Files"
Even if a network administrator has told you that the folder permissions are set correctly, they may not be. Use NotePad or another application to verify that file permissions are correct for each user by doing the following quick tests: Log into each user's PC as the user who will access the database and create a new text file that includes a few characters in the shared folder. Close the file and reopen it. Attempt to edit the data and save the change. Finally, use Windows Explorer to try to delete the file. You should be successful with all of these tests.
To determine which user is in your database at any given time, try the Workbench application written by Garry Robinson.
Keep a persistent connection open
See Access MVP Tony Toews's tip, "LDB locking which a persistent recordset connection fixes":
See the section, "Minimize the number of connections that are made from each client," in this Microsoft Knowledge Base article:
See Luke Chung's tip, "Tip #36: Increase Performance of Linked Databases":
Also see the fourth section, "Improve performance of linked tables," in this Microsoft Office Online tip:
An easy method of doing this is to create a table in the back end database that has just one record. For example, this record might indicate the latest version number of the front end database. Create a form that is bound to this table. Use VBA code or an Autoexec macro to open this form in hidden mode when the front end database is opened. That's all there is to it!
You should establish indexes on any fields that are used for sorting or as criteria in queries. Without indexes, the entire Recordset is transferred over the network prior to any filtering. Note: You do not need to index foreign key fields when these fields participate in a relationship created in Tools > Relationships, as long as you enforce referential integrity (RI). In this case Access automatically indexes the foreign key fields, although these indexes will not show up in the indexes window. You must enforce RI before Access will create a hidden index. (Without enforcing RI, creating relationships is little more than an exercise in drawing lines).
Avoid using Domain Aggregate Functions
Domain Aggregate Functions are used to look up a value or calculate statistics within a set of records -- a domain. These include DCount, DLookup, DMax, DMin, DSum, DAvg, DFirst, DLast, DStDev, DStDevP, DVar, and DVarP, all of which may adversely affect performance.
In queries that involve linked tables, avoid using VBA functions in query criteria, especially domain aggregate functions, such as DSum, anywhere in your queries. When you use a Domain Aggregate Function, Access may retrieve all of the data in the linked table to execute the query.
Use the JETSHOWPLAN registry flag to see Jet's plan of execution for your queries. Instructions can be found in the article, "Use Microsoft Jet's ShowPlan to write more efficient queries" by Susan Sales Harkins and Mike Gunderloy at http://builder.com.com/5100-6388-5064388.html. Download this zip file which includes a replacement for Listing A (which has a syntax error in the version offered with the article), two .reg files for toggling the registry setting on and off, and two examples of the output file, Showplan.out.
Note: The output files are text files that you can open with WordPad or NotePad. You can permanently associate this file extension with one of these applications, which will enable you to simply double-click on the Showplan.out file to open it.
Speed up Tab controls
If you have a Tab control that includes subforms, implementing the Tab-on-demand technique can be very helpful.
Close inactive sessions
Consider implementing a method of closing inactive sessions. This will help prevent a user from keeping the database open for long periods of time with no activity. See the Microsoft Knowledge Base article, "HOW TO: Detect User Idle Time or Inactivity in Access 2000":
I tend to use Const IDLEMINUTES = 20 (instead of the 1 minute setting shown in the Knowledge Base article), and I do not display a message box as shown in the article, since message boxes are modal and this would defeat any attempt to close:
'Dim strMessage As String
'strMessage = "No user activity detected in the last" & vbCrLf
'strMessage = strMessage & sngExpiredMinutes & " minute(s)!"
'MsgBox strMessage, vbInformation, "No Sign of Activity!"
Optimize Access and Jet
See the Microsoft Knowledge Base article, "How to keep a Jet 4.0 database in top working condition":
See the Microsoft Knowledge Base article, "How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clients":
Optimize a multi-user environment for a client/server configuration
Other issues to be aware of include:
The path to the database should not traverse through several folders; and
Folder names should not exceed eight characters; and
Make sure that your antivirus software is not configured to scan network files.
These tips and others are discussed in the following articles:
See Access MVP Tony Toews's article, "Microsoft Access Performance FAQ":
See the Microsoft Knowledge Base article, "How To Improve Performance of Applications Using Jet 4.0":
To avoid page-level locking with DAO 3.6, see the Microsoft Knowledge Base article, "PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60"
See the Microsoft Knowledge Base article, "ACC2000: Optimizing for Client/Server Performance":
This applies to Access 2007 and 2003 as well. This article includes the following advice: "NOTE: If you make changes to fields or indexes on the server, you must relink the remote tables in Access." This is due to the current links caching structural and connection information for the remote tables at the time of the link, and this information never gets updated. To relink the tables, delete any existing linked tables. Compact the database. Then re-establish the linked tables using File > Get External Data > Link Tables...
When you specify "Open databases using record-level locking" (Tools > Options | Advanced tab) you should realize that this is a request, not a demand. Michael Kaplan has recently posted some information on this topic on his blog site:
See the Microsoft Knowledge Base article, "Files on Network Shares Open Slowly or Read-Only or You Receive an Error Message":
Configure laptops for network use
And, for good measure, if you have any laptop users who plug their laptops into a docking station, you might want to be aware of a previous issue, which I have reprinted below.
"The user had a laptop. When he plugged the laptop in without the docking station, it worked fine, but when docked, it slowed to a crawl. It turned out to be the network cards. The profile had to be changed because the network cards were working against each other. They disabled the internal network card for the 'IN DOCK' profile. Once that was done, the connectivity was great."
Also, I strongly encourage advising laptop users with wireless connections not to open a Jet database until they establish a hard-wired network connection. Jet databases are very susceptible to corruption from network disconnections, so it's best to use a stable network connection, not wireless connections. Currently, there is no way to automatically detect a wireless connection in order to block these users, so the best policy is to train users to use only wired connections to Jet databases.
Speed Up Reports (By reducing the page calculations)
You can move (or delete) the
="Page " & [Page] & " of " & [Pages]
control in a report down to the Report footer as follows
=IIf([Page]>1,"There are " & [Pages] & " pages in this report.","")
Created: Mar. 9, 2004
Last Updated: Jan. 23, 2008
Copyright © 2004 - 2008 Tom Wickerath. All rights reserved. Reprinted at vb123.com by permission.