Implementing a Successful Multi-User Access/JET
By Tom Wickerath, Microsoft Office Access MVP
"The Access 2007 version of this article is
in The Toolbox"
Hint: "To find tips to speed up
your database the easy way, purchase
Analyser" Garry 2007
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 mult-iuser 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":
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 mult-iuser 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
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
To determine which user is in your database at any given time, try the
Workbench application written by
Keep a persistent connection open
See Access MVP Tony Toews's tip, "LDB locking which a persistent recordset
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).
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.
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
'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
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
Optimize a mult-iuser 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
This applies to Access 2002 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
"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.","")
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
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
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
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
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
Other Pages at VB123.com That You May
Want To Visit
Read some of
Garry's tips on indexes
Queries To Impress Your Boss
Access Traps for
the Naïve Developer
Citrix / Remote Desktop
– for an Access-developer
Click on the
button for the next
help page in this Access Loop.