Microsoft Access Performance Tuning

Slow systems frustrate your staff and your customers, and they cost you money. People stop using the database properly, start keeping their own spreadsheets, and the data stops being trustworthy.

Access can be quick. Most of the databases we’re asked to look at are slow for reasons that can be fixed in days rather than replaced. ☎

Our 4 point plan

1. Measure what’s actually slow

• Time the things people complain about: opening the database, loading a form, running a report, searching
• Find out whether it’s slow for everyone or one person, all day or only at 9am
• Establish whether the problem is the database, the network, or the PC

2. Queries

• Analyse the most used and most expensive queries
• Filter at the source instead of pulling whole tables across the network and filtering afterwards
• Replace domain functions (DLookup, DSum) buried in queries with joins
• Pass-through queries and stored procedures where there’s a SQL Server back end
• Stop forms loading every record on open when they only need today’s

3. Indexes and structure

• Index the fields actually being joined and searched on, and remove the ones that only slow writes down
• Fix data types that force Access to convert on every comparison
• Turn off subdatasheets, which silently open extra recordsets you never asked for
• Compact and repair on a schedule, because bloat costs real speed

4. Settings and environment

• Default Access and network settings are not tuned for a shared database
• Turn off Name AutoCorrect, which quietly tracks every object every time
• Hold a persistent connection to the back end so Access stops reopening the lock file
• Front end on each PC, back end on the server, never both in one file on a share
• Antivirus exclusions for the database and lock files
• Sort out the wireless, VPN or NAS problem if that’s what it turns out to be

Fine tuning the configuration alone often gets 20–30% back before anyone touches the code.

When tuning isn’t the answer

We’ll tell you if you’ve reached the point where no amount of tuning helps: too many concurrent users for a file-based database, a back end approaching 2GB, or people needing at it from outside the office.

At that point the fix is moving the data rather than optimising around it → Access migration and upgrades

If the symptoms are crashes and corruption rather than slowness, that’s a different problem →Access repair and recovery

Not sure which you’ve got? An Access health check will tell you, and it’s the cheaper way to find out.