Fix Slow MS Access Database — 9 Expert Performance Fixes (Not Guesses)

Diagnose Before You Touch Anything

Every "fix slow Access database" thread on the internet starts with the same mistake: running Compact & Repair before identifying the actual bottleneck. Compact helps with file bloat. It does nothing for a missing index, an unbounded record source, or an ODBC driver that regressed after an Office update. Apply the wrong fix and you waste time — or worse, you compact a file that had recoverable corruption and make it smaller and more broken.

The right diagnostic sequence is:

1. Reproduce on Ethernet or RDP — if the problem disappears, it is a network/topology problem (Fix 8), not a code problem. Stop here and fix the network path before touching the database.
2. Check the .lacdb file — open File Explorer on the back-end folder. If the .lacdb has stale entries or grows/shrinks rapidly, you have lock churn from crashed clients or antivirus interference (Fix 8).
3. Note when it started — if performance degraded after a specific Windows or Office update, check the ODBC driver first (Fix 7) before profiling queries.
4. Name the slowest five objects — form names, report names, or specific query names with their measured open times. "The whole database is slow" is not actionable. "frmInvoiceList takes 18 seconds to open and timed out twice this week" is.

Need help running this triage remotely? MS Access performance optimization engagements start with a free baseline — you send the object names and timings, we identify the fix tier before billing anything.

Quick Wins — About 10 Minutes Each

• Name AutoCorrect → OFF while triaging: File → Options → Current Database → uncheck all Name AutoCorrect options. Stops hidden rename ripple that adds overhead to every object load.
• Subdatasheet Name → [None] on every high-traffic table (see Fix 2 below).
• Compact & Repair on a verified backup — never on the only copy, and never as a loop when the catalog is suspect. See MS Access database repair for when Compact is the wrong move.
• Debug → Compile [ProjectName] in VBA Editor — saves a compiled front end and eliminates first-call VBA overhead (see Fix 6).

Fix 1 Split the Database — FE/BE Architecture

If your entire .accdb file lives on a shared network drive that every user opens directly, this is the single change with the highest performance impact. Every ACE page read becomes an SMB round trip. Every user opening the same file increases lock contention. Every combo box, subform, and report pulls data across that network path.

The fix: split the database into a back-end file (tables only, stays on the server) and a front-end file (forms, queries, reports, VBA — each user runs their own local copy linked to the shared back end). Only data crosses the network. The UI runs locally.

Fix 2 Disable Subdatasheets on High-Traffic Tables

When a table's Subdatasheet Name property is set to Auto (the Access default), the engine silently looks up related tables and loads related rows every time you navigate to a record in datasheet view — including when forms open in the background. Over a network, this is a hidden round trip per record you touch.

1. Open the table in Design View.
2. In the property sheet, set Subdatasheet Name to [None].
3. Also clear Subdatasheet Height and uncheck Subdatasheet Expanded if set.
4. Repeat for every high-traffic table. Save and compact the FE copy.

Most users notice this change immediately on tables that have many records.

Fix 3 Index Foreign Key Columns Used in Joins

The ACE query optimizer uses nested loops for joins. Without an index on the inner join column, every record in the outer table triggers a full scan of the inner table. On a table with 50,000 rows this produces 50,000 × 50,000 comparisons — which is why a "simple" query takes minutes.

Rule: every foreign key column used in a JOIN should be indexed. Add indexes with Duplicates OK (not unique). Do not index columns with very low cardinality (Yes/No, Status with 3 values) — those help nothing and slow writes.

Fix 4 Narrow Bound Recordsets — No SELECT * on Forms

A form with SELECT * FROM tblOrders as its RecordSource pulls every column in every row across the network on open — including columns the form never displays. On a 100,000-row table with 40 columns, this is an enormous amount of unnecessary data movement.

Replace every form RecordSource with a saved query that:

• Returns only the columns the form actually binds to controls
• Filters to a relevant subset: current user, current month, open-status records only — whatever makes sense for the task
• Uses a saved query (not inline SQL) so the query plan is cached and the form opens faster on repeat use

For data-entry forms that need a blank new record on open, setData Entry = Yes in form properties — this opens the form to a blank record without loading the full table.

Fix 5 Query Surgery — Sargable Predicates and DLookup Elimination

Two query patterns cause the most Access performance problems in production:

Fix 6 Reset VBA Compile State

Access stores VBA code in two states: source text and compiled p-code. When the compiled state is out of sync with the source — which happens after adding or editing modules without compiling, or after a Compact that preserved the source but discarded the p-code — VBA recompiles every module the first time it is called. This produces slow first-run behavior that feels random because it happens per-session.

1. Open the VBA Editor (Alt+F11).
2. Click Debug → Compile [ProjectName]. Fix any compile errors that surface.
3. Save (Ctrl+S).
4. Compact a copy of the front end immediately after (not before — compacting before compiling discards p-code).

If Debug → Compile is grayed out, the project is already compiled. If it throws errors, fix the missing references first (Tools → References in VBA Editor).

Fix 7 Update the ODBC Driver — SQL Linked Table Slowness After Office Update

If your Access database links to SQL Server and performance degraded after a specific Office 365 update, this is the fix most people miss. The legacy SQL Server ODBC driver (DRIVER=SQL Server) regressed in multiple Office 365 Current Channel builds starting in late 2023 — causing linked table operations to take 10–100× longer than expected.

The fix: re-link your SQL Server tables using DRIVER=ODBC Driver 17 for SQL Server (or ODBC Driver 18). Download the driver from Microsoft if not already installed, then use External Data → ODBC Database to re-establish the linked table connections with the new driver.

Fix 8 Wi-Fi / VPN / .lacdb Lock Churn — Test Topology Before Rewriting Code

File-backed ACE uses many small SMB read operations. On a gigabit wired LAN, each operation takes microseconds. On Wi-Fi with 50ms jitter, the same operation takes 50–200ms — and lock holds extend proportionally, blocking other users.

Always reproduce the slowness on Ethernet or RDP before rewriting any code. An RDP session host colocated with the file server collapses latency to near-zero because the ACE engine runs next to the data — not across a lossy Wi-Fi last hop. If performance is acceptable on RDP, the database code is fine. The problem is the network path.

Fix 9 Persistent DAO Connection — Eliminate Per-Session Reconnect Tax

When Access opens a linked table for the first time in a session, it must locate the back-end file, authenticate, and warm up the connection. On a split JET/ACE back end this adds a noticeable delay to the first form that touches linked data. The fix is to hold a tiny open DAO.Recordset against a 1-row ping table in the back end from startup — this keeps ACE's data path warm for the entire session.

' basNetworkPing.bas — persistent connection pattern for linked tables
' Replace tblLinkPing with a 1-row table in your BACKEND, linked read-only in FE.
' Call PersistentConnection_Open from your startup form; Close on app shutdown.
Option Compare Database
Option Explicit

Private m_rsPing As DAO.Recordset

Public Sub PersistentConnection_Open()
    On Error GoTo Fail
    If Not (m_rsPing Is Nothing) Then Exit Sub          ' already open — exit
    Set m_rsPing = CurrentDb.OpenRecordset( _
        "SELECT TOP 1 PingID FROM tblLinkPing", _
        dbOpenSnapshot)
    Exit Sub
Fail:
    ' Log to tblErrorLog — do NOT block startup
End Sub

Public Sub PersistentConnection_Close()
    On Error Resume Next
    If Not m_rsPing Is Nothing Then m_rsPing.Close
    Set m_rsPing = Nothing
End Sub

Wire PersistentConnection_Open from your startup form's Form_Open event, and PersistentConnection_Close from your application shutdown routine. The ping table needs only one column (PingID AutoNumber) and one row.

Note: this pattern helps with linked-table reconnect latency. It will not fix slow queries, missing indexes, or a non-split database. Apply after Fixes 1–5.

Before vs. After Optimization — Real Benchmarks

Composite SMB profile from US client engagements. Your baseline must be measured — assume nothing until you have timed the specific slow objects on your actual hardware and network path.

When Tuning Stops Being Honest — Migrate to SQL Server

All nine fixes above work within the ACE/JET architecture. They are the right moves for most US businesses running Access databases with up to a few million rows and 10–15 concurrent users. Beyond certain thresholds, tuning buys time but does not solve the structural problem:

• Back-end file regularly exceeds 1 GB of real data (not post-compact bloat) — you are compacting around a ceiling.
• 5+ concurrent writers on the same high-contention tables — page-level locking is the limit, not your query design.
• Compliance requires server-side transaction logs, row-level auditing, or column-level encryption — ACE does not provide these natively.
• Ignoring chronic .lacdb / force-kill patterns turns performance debt into corruption risk — which is more expensive than a migration audit.

If your Access system is past these thresholds, the right next step is a written migration assessment — migrate Access database to SQL Server — not another round of query tuning. The Access front end stays; only the data engine changes.