Why Your MS Access Database Is Slow — And How to Fix It

Symptom, cause, and fastest fix

Match what you are seeing to the row below before you touch anything — it saves you from indexing the wrong field or splitting a database that was never the problem.

Why an Access database becomes slow

Jet/ACE executes the SQL you give it faithfully. When the plan behind that SQL is bad, you pay for it in seconds per interaction, not milliseconds. I have watched teams replace workstations and add RAM while a single CRM or inventory form kept pulling an unfiltered dynaset across 80,000-120,000 rows on every tab click — the hardware was never the bottleneck.

• Unindexed fields in filters and joins — a frequent WHERE or JOIN on a non-indexed column forces a full table scan. On 50k-100k rows, that is the difference between a sub-second seek and a 6-15 second form open.
• Heavy queries across large tables — nested subqueries and nonselective joins multiply work, and one bad saved query reused by five forms spreads the pain everywhere.
• Full-table-bound forms and reports — a continuous form or combo box bound to an unfiltered SELECT * drags the entire table to the client before you ever sort or filter in the UI.
• Network latency on a split back-end — chatty SQL over a weak Wi-Fi path looks like “network slowness” but is still a design problem underneath it.
• File bloat — deleted rows and temp growth leave dead space in an .accdb that has never been compacted, slowing I/O and backups alike.

For the full breakdown of why large datasets specifically expose these issues, see why Access slows down with large data.

You cannot fix what you have not measured. Guessing leads to random indexing or an unnecessary SQL Server migration that leaves the real bottleneck in place. Rank the slowest forms and reports by user complaints and observed wait time, then check what row count each one actually pulls versus what the user needs to see on screen.

A simple VBA timer around the suspect query tells you exactly where the time is going, without guessing from the UI:

Public Sub TimeQueryPerformance(queryName As String)
    Dim startTime As Single
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    startTime = Timer
    Set db = CurrentDb
    Set rs = db.OpenRecordset(queryName, dbOpenSnapshot)
    rs.MoveLast   ' forces the full recordset to actually populate
    Debug.Print queryName & ": " & Format(Timer - startTime, "0.000") & _
        " sec, " & rs.RecordCount & " rows"
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Real example: A job-costing form bound to a query returning 100,000 detail lines opened in 12 seconds. Replacing it with a filtered key set (current job only) dropped open time under one second without changing a single business rule.

File bloat is the quiet performance killer nobody notices until the back-end is 400MB of mostly dead space. A programmatic compact, run on the back-end from a small utility database, reclaims that space:

Public Sub CompactDatabase()
    Dim strSource As String
    Dim strTemp As String

    strSource = CurrentDb.Name
    strTemp = Left(strSource, Len(strSource) - 6) & "_temp.accdb"

    DBEngine.CompactDatabase strSource, strTemp
    Kill strSource
    Name strTemp As strSource

    MsgBox "Database Compacted Successfully"
End Sub

Compact requires exclusive access, so this only runs cleanly with every user out of the file. For the scheduling approach, permission errors, and the “file in use” workaround, see the full compact and repair guide.

Scenario: roughly 120,000 line records, 10 concurrent users on a split database, file size around 250MB before cleanup.

Before: stock lookup and receiving forms took 8-12 seconds to open; users force-quit weekly; month-end reporting froze other sessions.

Intervention: indexed part number and location keys, rewrote the slowest join queries, moved long-running reports to pre-aggregated tables, scheduled back-end compact, and tightened form record sources to the active warehouse only.

After: interactive screens under one second in normal use, stable multi-user behavior, and predictable overnight batch windows instead of random daytime lockups.

When it is not actually a performance problem

Slow and unstable are not the same issue. If the database is also throwing “unrecognized database format” errors, forms will not open at all, or it crashes outright rather than just running slow, that points to corruption or a size-related crash rather than a pure performance gap:

• Database crashes or will not open — see fixing an Access database that keeps crashing.
• File is corrupted or shows “unrecognized format” — see Access corruption repair or Access data recovery.
• File is approaching the 2GB ceiling — see Access database size issues.

Common mistakes we see in the field

• No indexing on the fields every query actually filters on.
• Excel-style flat sheets imported as one giant table with repeated headers and no keys.
• Loading entire tables into forms “because it used to be fast when we had 2,000 rows.”
• Refusing to split front-end and back-end, so every schema tweak becomes a fight.
• Ignoring compact & repair for years while imports and deletes quietly bloat the file.

When optimization is not enough

If your Access database is past 300-500MB with multiple active users and still struggles after indexing, query fixes, and compacting, the problem is structural, not temporary. At that point you need a deliberate plan — often upsizing the data layer to SQL Server while keeping Access as the front-end, rather than more tuning.

For that migration path, see Access-to-SQL Server migration. If you want us to run the full diagnosis and fix end to end rather than doing it yourself, that is exactly what our Access performance optimization service covers.

Frequently asked questions

Related resources

• Fixing slow Access queries — query-level rewrites and execution plans.
• Access performance optimization (service) — a dedicated engagement for tuning and remediation.
• Access-to-SQL Server migration — when optimization alone stops being enough.
• VBA automation — macros, integration, and scheduled maintenance.