10 Ways to Speed Up a Slow Microsoft Access Database

A slow Microsoft Access database is one of the most frustrating things in the office. What used to open in seconds now takes a minute. Queries that ran instantly now spin for thirty seconds. Before you blame Access itself, know this: most performance problems are fixable, and the fixes are often simpler than you expect.

1. Add Indexes to Fields You Query and Sort On

This is the single most impactful optimization you can make. An index is a sorted lookup structure that lets Access find records without scanning the entire table.

Add an index to any field that you:

• Use in a WHERE clause
• Join on between tables
• Sort by in a query or report
• Use in a combo box row source

To add an index, open the table in Design View, click the field, and set the Indexed property to "Yes (Duplicates OK)" or "Yes (No Duplicates)" in the Field Properties panel.

Caution: Do not index every field. Indexes speed up reads but slow down writes. Index the fields you actually filter and sort on.

2. Compact and Repair the Database Regularly

Every time you delete records or objects, Access leaves behind empty space in the file. Over time, this bloat slows everything down. Compacting reclaims that space and reorganizes the file for faster access.

Go to File → Info → Compact & Repair Database. For a database in active use, compact it weekly. You can also set it to compact automatically on close: File → Options → Current Database → Compact on Close.

3. Split the Database

A single-file Access database shared over a network is a performance disaster. Every time someone opens a form, Access has to transfer the entire form definition — and all the data — across the network.

The solution is to split the database into two files:

• Backend (.accdb) — contains only the tables, stored on the network share
• Frontend (.accdb) — contains forms, queries, reports, and macros, stored locally on each user's machine

With a split database, only the data travels over the network. Forms and queries run locally. This can improve performance by 10x or more for multi-user databases.

Use the Database Splitter wizard: Database Tools → Move Data → Access Database.

4. Avoid SELECT * in Queries

When you write SELECT * FROM Orders, Access retrieves every field in the table — even the ones you do not need. For wide tables with many fields, this is wasteful.

Instead, name only the fields you actually need:

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= #1/1/2026#

This reduces the amount of data Access has to read and transfer.

5. Use Bound Columns Wisely in Combo Boxes

Combo boxes with large row sources are a common performance culprit. If your combo box is pulling from a table with 50,000 records, it loads all 50,000 every time the form opens.

Fixes:

• Add a WHERE clause to the combo box's Row Source to limit the records
• Set Auto Expand to No if you do not need it
• Use a query with only the fields the combo box needs (not SELECT *)

6. Reduce the Number of Controls on Forms

Forms with hundreds of controls — especially subforms, combo boxes, and calculated controls — are slow to open and slow to navigate. If you have a form that has grown organically over years, audit it. Remove controls that are rarely used. Move less-used fields to a separate tab or a secondary form.

7. Use Queries Instead of Calculated Controls Where Possible

Calculated controls (text boxes with expressions like =[Quantity]*[UnitPrice]) are evaluated for every record as you scroll. For large recordsets, this adds up.

Move calculations into the query that feeds the form. The calculation runs once at query time, and the form just displays the result.

8. Set the Recordset Type Appropriately

By default, Access forms open as Dynaset recordsets, which allow editing. If a form is read-only (a report-style form for viewing data), set the Recordset Type property to Snapshot. Snapshots are faster because Access does not need to maintain the overhead required for editing.

Open the form in Design View, open the Property Sheet, click the Data tab, and change Recordset Type to Snapshot.

9. Avoid Domain Aggregate Functions in Queries

Functions like DLookup(), DCount(), DSum(), and DAvg() are convenient but slow. Each call opens a separate recordset behind the scenes. If you use them in a query that returns 1,000 rows, Access runs 1,000 separate lookups.

Replace domain aggregates with subqueries or JOINs wherever possible. A properly written JOIN will almost always outperform a DLookup.

10. Archive Old Data

The simplest performance fix is often the most overlooked: move old data out of your active tables. If your Orders table has ten years of history but you only ever query the last two years, archive the older records to a separate table or database.

Create an archive table with the same structure, run an Append query to copy old records into it, then run a Delete query to remove them from the active table. Your queries will run against a fraction of the original data volume.

Conclusion

Access performance problems are almost always solvable. Start with indexing and compacting — those two steps alone fix the majority of slow databases. If you are on a network, split the database. Then work through the query and form optimizations as needed. A well-tuned Access database can handle millions of records and dozens of simultaneous users reliably.