Home

Implementing a Successful Multiuser Access/JET Application
 

By Tom Wickerath    

Use these techniques to optimize your Access/Jet applications.

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 multiuser 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 article:  "Help Access run faster"
https://support.office.com/en-US/article/Help-Access-run-faster-F6827763-BB5C-4F48-8457-7A14ADDAB6BE#bm1_1

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":
https://support.microsoft.com/kb/275085

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 multiuser 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 multiuser 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 multiuser 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"
     https://support.microsoft.com/kb/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 AppUser application, which you can download from the MVPS.org Web site:
http://www.mvps.org/access/modules/mdl0055.htm
 

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:
https://support.microsoft.com/kb/303528/EN-US/#15

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

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.  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. 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": https://support.microsoft.com/kb/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":
https://support.microsoft.com/kb/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":
https://support.microsoft.com/kb/889588
 

Optimize a multiuser 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":
https://support.microsoft.com/kb/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"
https://support.microsoft.com/kb/306435

See the Microsoft Technet article, "Optimizing Microsoft Office Access Applications Linked to SQL Server":
https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx

NOTE: If you make changes to fields or indexes on the server, you should relink the remote tables in Access. This is due to the current links caching information for the remote tables at the time the link was created; table and view metadata does not get updated automatically. 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... (Access 2003 and earlier) or Database Tools tab / Linked Table Manager group (Access 2007 and later).

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 posted some information on this topic on his blog site:
http://archives.miloush.net/michkap/archive/2005/10/19/482694.html

See the Microsoft Knowledge Base article, "Files on Network Shares Open Slowly or Read-Only or You Receive an Error Message":
https://support.microsoft.com/kb/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.
 

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:  May 4, 2017

Copyright © 2004 - 2017 Tom Wickerath. All rights reserved.

@	MEMBER OF PROJECT HONEY POT Spam Harvester Protection Network provided by Unspam

Top    Home