MS Access Performance Optimization: Fix Slow Databases With Measurable Results

1. Performance Diagnostic Audit (2–4 Hours)

Every engagement starts with a full diagnostic. We use Access's built-in Performance Analyzer as a starting point, but we go much further: timing individual queries with VBA's Timer() function, examining execution plans, reviewing all table indexes against actual query patterns, checking schema normalization, and reading VBA modules for anti-patterns.

You receive a written findings report — with measured baseline times, identified bottlenecks ranked by impact, and a recommended fix sequence — before we write a single line of optimization work. You approve the scope and cost before anything changes.

2. MS Access Index Optimization

Indexing is the single highest-leverage fix in almost every slow Access database. The Jet/ACE query engine uses indexes for WHERE clause filtering, JOIN resolution, ORDER BY sorting, and GROUP BY aggregation. If an index doesn't exist for a field the engine needs during query execution, it performs a full table scan — reading every record regardless of result set size.

We identify every field that appears in a WHERE clause, JOIN condition, ORDER BY, or foreign key constraint across every query and form record source in your database. We add the missing indexes, remove any redundant indexes that slow down write operations without improving reads, and set composite indexes where multi-column filtering patterns make them beneficial.

Important note: Access has a hard limit of 32 indexes per table. If you are near that limit, we prioritize by query frequency and impact rather than adding every possible index.

3. Query Rewriting & MS Access SQL Optimization

Slow Access queries have specific, identifiable causes. The most common patterns we fix:

• Cartesian joins: Two tables in a query with no join condition produce a row count equal to Table A rows × Table B rows. A query joining a 5,000-row table to a 10,000-row table without a join condition processes 50 million rows.

• Calculated expressions in WHERE clauses: Using a function or expression on an indexed field (e.g., WHERE Format(DateField, "yyyy") = "2024") forces a full table scan because Access cannot use the index on the raw field value.

• Correlated subqueries in SELECT lists: A subquery that executes once per row in the outer result set can turn a fast query into one that runs thousands of separate operations.

• Domain aggregate functions in queries: DLookup(), DSum(), DCount() and similar domain aggregate functions run a separate database query per record. A SELECT list with DLookup() on a 10,000-row result set runs 10,000 individual queries.

• Missing TOP or filter on large recordsets: Forms and reports that open on queries returning 50,000+ rows when they only display 20 at a time send all 50,000 rows across the wire unnecessarily.

We rewrite each slow query from the SQL up, test execution time before and after, and document every change so your team can maintain it going forward. See our detailed guide on fixing Access slow queries for more on specific SQL patterns.

4. Database Compaction & Fragmentation Repair

The Jet/ACE database engine does not reclaim disk space when records are deleted — it marks pages as available but leaves gaps in the file. Over months or years of normal use, a database that started at 50MB can grow to 400MB or more without a meaningful increase in actual data, purely from accumulated deleted-record space and fragmented page allocation.

This matters for performance because Access reads data in 4KB pages. A fragmented file forces the engine to jump across non-contiguous pages rather than reading sequentially, which is significantly slower — especially on network drives. Compacting rewrites the entire file sequentially and can restore the read performance the database had when it was first deployed.

We compact and repair the database, verify integrity after compaction, and set up an automated compaction routine (either via a Startup macro or a Windows Scheduled Task using the Access /compact command-line switch) so fragmentation doesn't accumulate again. See our page on Access database size problems for more detail on how fragmentation grows and how automated compaction prevents it.

5. Split Architecture: Front-End / Back-End Setup

If your database is stored as a single .accdb file on a network share and accessed by multiple users simultaneously, the architecture itself is the bottleneck. When Access queries data in a single-file setup, the Jet/ACE engine runs the query engine on each user's local machine — which means full table data must travel across the network for every query before local filtering can occur. On a network with 10 users each running queries simultaneously, this creates serious contention.

The fix is a split architecture: the back-end file contains only tables and lives on the server; the front-end file contains forms, queries, reports, and VBA and is stored locally on each user's machine. With a split database, each user's local Access instance sends only the specific records it needs across the network — rather than the full table — dramatically reducing traffic.

We perform the split, relink all table connections, test thoroughly across all user machines, and configure an auto-relink routine that handles path changes automatically. This is the most impactful single change for multi-user Access databases, and it requires zero changes to your forms, reports, or queries. Read more about multi-user best practices on our Access multi-user database page.

6. Form & Report Record Source Tuning

Forms are one of the most commonly overlooked sources of MS Access performance problems. When a form's Record Source is set directly to a large table with no WHERE clause, Access loads every record in that table into the dynaset before displaying anything. A table with 200,000 records means 200,000 records travel across the wire and get loaded into memory even if the user only needs record #147,832.

We convert data-entry forms to open unbound (no record loaded), then pull only the requested record on demand. For continuous forms and subforms displaying lists, we add parameterized record sources so only the filtered subset loads. For reports, we optimize the record source query, add pagination where appropriate, and convert DLookup() calls in calculated controls to proper joins in the underlying query.

7. VBA Code Performance Optimization

VBA is a powerful tool for MS Access automation, but common coding patterns that seem natural are extremely slow at scale. The biggest offender is the row-by-row loop: VBA code that opens a recordset, loops through every record, reads a value, performs a calculation, and updates another table — one record at a time. The equivalent SQL UPDATE or INSERT…SELECT statement executes in a fraction of the time.

We identify VBA modules that process data record-by-record and replace them with set-based SQL operations wherever possible. We also fix connection leaks (recordsets opened inside loops but never closed), replace DLookup() calls in loops with temporary table joins, and eliminate unnecessary screen repaints in form-based automation. For complex VBA automation requirements, see our Access automation services and VBA automation pages.