Use these techniques to optimize your Access/Jet applications

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":
http://support.microsoft.com/?id=290181

For additional reasons why one should avoid this default option, see Access MVP Allen Browne's tip, "Failures caused by Name Auto-Correct":
http://allenbrowne.com/bug-03.html

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:
http://support.microsoft.com/?id=275085

Note: You must disable the Track Name AutoCorrect option first. Otherwise, a subdatasheet property set to [None] can easily revert back to [Auto].

Note:  Sometimes its good to have subdatasheets in smaller databases as in the following articles
Exploring Your Data With Subdatasheets
SubDatasheets In Access - How To Use Them

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"
http://support.microsoft.com/?id=299373

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":
http://www.granite.ab.ca/access/performanceldblocking.htm

See the section, "Minimize the number of connections that are made from each client," in this Microsoft Knowledge Base article:
http://support.microsoft.com/kb/303528/EN-US/#15

See Luke Chung's tip, "Tip #36: Increase Performance of Linked Databases":
http://www.fmsinc.com/free/NewTips/Access/LinkedDatabase.asp

Also see the fourth section, "Improve performance of linked tables," in this Microsoft Office Online tip:
http://office.microsoft.com/en-us/assistance/HP051874531033.aspx

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!

Use indexes

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 JETSHOWPLAN

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.

The software for this is available in the download for this article
Please see the attached Word document that discusses this issue.

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":
http://support.microsoft.com/?id=210297

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:

Sub IdleTimeDetected(sngExpiredMinutes)
  '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!"
  Application.Quit acSaveYes
End Sub

Optimize Access and Jet

See the Microsoft Knowledge Base article, "How to keep a Jet 4.0 database in top working condition":
http://support.microsoft.com/?id=303528

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":
http://support.microsoft.com/?id=889588

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":
http://www.granite.ab.ca/access/performancefaq.htm

See the Microsoft Knowledge Base article, "How To Improve Performance of Applications Using Jet 4.0":
http://support.microsoft.com/?id=240434

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"
http://support.microsoft.com/?id=306435

See the Microsoft Knowledge Base article, "ACC2000: Optimizing for Client/Server Performance":
http://support.microsoft.com/?id=208858

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:
http://blogs.msdn.com/michkap/archive/2005/10/19/482694.aspx

See the Microsoft Knowledge Base article, "Files on Network Shares Open Slowly or Read-Only or You Receive an Error Message":
http://support.microsoft.com/?id=814112

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.","")

Check hardware

As a follow-up to complaints of slow performance in Access, you might want to check out the hardware too. This has nothing to do with Access in particular, but can increase your computer's performance for any application you run.

Clean out the temporary files
Shut down all application software (Word, Excel, Access, Internet Explorer, etc.). Navigate to your temp folders and delete all of the files present.

C:\Documents and Settings\YourUserName\Local Settings\Temp

and

C:\Documents and Settings\YourUserName\Local Settings\Temporary Internet Files

where YourUserName is what you use to log into Windows. Note: The Temporary Internet Files folder can also be emptied by right-clicking the IE desktop icon, left-click on properties, and then click on the Delete Files... command button. Place a check in the "Delete all offline content" option.

If you use Netscape, you should clear the Netscape disk cache as well. Click on Edit > Preferences..., select the plus sign next to Advanced, select Cache, and click on the "Clear Disk Cache" button.

Defrag the hard drive

Many PC's are probably not being defragged on a regular basis (if ever). Schedule a defragment operation after cleaning out the temporary files, compacting your Access databases, and compacting any personal folders in Outlook or Outlook Express.

You might want to schedule the defrag to run one time per month, during a time that you know you will not be needing your PC. Recommendation: Close all applications, including Outlook, before running the disk defragmenter. Disable the antivirus software, the screen saver and any power saving settings.



Created: Mar. 9, 2004
Last Updated: Jan. 23, 2008

Copyright © 2004 - 2008 Tom Wickerath. All rights reserved. Reprinted at vb123.com by permission.

 

About the author:

Tom Wickerath is a chemist at The Boeing Company in Seattle, Washington, USA. He works in the Analytical Chemistry Group of Boeing's Materials & Process Technology (M&PT) organization. In the early 1990's, Tom became interested in the use of database technology to avoid many of the inefficiencies involved in using spreadsheets for the analysis of large amounts of data.

Tom has taught Microsoft Access courses to college students for three years at Bellevue Community College in Bellevue, Washington. He has also been an active member of the Pacific Northwest Access Developer's Group (PNWADG) since the days of Access 2.0 and served as an officer of this group from 1998 to 2002. As a longtime Access expert, Tom has given presentations on complex Access subjects, including tools designed for Access developers, to the Seattle Access Group, the PNWADG and to Boeing Company personnel.

Tom has provided his expertise to Microsoft Access developers and users for years in the Microsoft Access Newsgroups in UseNet, and he was awarded MVP for Microsoft Access in April, 2006. Check the Microsoft.Public.Access Newsgroup for Tom's latest expert advice and feel free to post any questions in these Newsgroups.

Special Note From The Author: "If you find that any of these tips save you a lot of time and frustration with your database development, please consider making a tax deductible contribution to the Northwest Kidney Center, located in Seattle, Washington, USA. I've seen first hand how valuable their work is. With your help, they can do more research in the treatment and prevention of kidney disease, provide increased financial assistance to patients in need, and generally make kidney patients' lives more comfortable. You can even use PayPal to make a quick and easy donation online, and you'll feel better knowing that you have contributed to a better quality of life for those in need. Thanks."

-- Tom Wickerath

http://www.nwkidney.org/nkc/howYouCanHelp/donate/index.html

For questions regarding this tutorial, please contact Tom at:



Tom does not accept unsolicited requests for help. Contact Tom only if you have questions or feedback on one of his articles or tips, or you have been specifically invited by Tom in a newsgroup posting to contact him. If your question references a question in the newsgroups, please include the URL to the post.

Other Pages at VB123.com That You May Want To Visit

Read some of Garry's tips on indexes

Tricky Queries To Impress Your Boss
Access Traps for the Naïve Developer
Citrix / Remote Desktop – for an Access-developer

External Links

http://www.fmsinc.com/MicrosoftAccess/Performance.html

 

Click on the button for the next help page in this Access Loop.