Why an Access database slows down as more people use it

A familiar story: an Access database worked well for years, the team grew, and now forms take ten seconds to open and everyone has learned to wait. The usual reaction is to blame the server or the network. Sometimes that is right, but more often the cause is in how the application is built, and it can be fixed.

Everyone is opening the same file

The first thing to establish is whether the database is split. A properly arranged Access application has two files:

• a back end on the server, containing only the tables;
• a front end containing the forms, reports, queries and code, with a separate copy on each user’s PC.

If all users open one shared file from the server, every form and report is dragged across the network each time it is used, and the users contend with one another for the same objects. This arrangement is also the leading cause of corruption. Splitting the database and giving each person a local front end is often the largest single improvement available.

Forms that load everything

A form bound directly to a table of 200,000 records asks Access to prepare all of them, even though the user wants one. With one user and a small table nobody notices. With many users and years of data it becomes the main delay.

The remedy is to open forms on only the records needed: a single customer, this month’s orders, the result of a search. The same applies to combo boxes and list boxes that load tens of thousands of rows.

Missing indexes

An index lets the database find matching records without reading the whole table. Fields used to join tables, to filter and to sort should normally be indexed. Access indexes primary keys automatically, but the fields used in searches and reports are frequently left without, and a query that reads a whole table across the network to find twelve rows is slow however good the hardware is.

Queries that do the work in the wrong place

Some query designs force Access to process records one at a time:

• calling VBA functions for every row;
• using lookup functions such as DLookup inside a query or a continuous form;
• stacking queries on top of other queries several layers deep.

Each is fine on a small table and expensive on a large one. Rewriting a handful of the worst queries often does more than any hardware upgrade.

The connection keeps being opened and closed

Each time a front end opens the back end, Access has to create and manage a lock file on the server. If nothing in the front end keeps a connection open, this happens repeatedly throughout the day. Holding one connection open for the whole session, a long-established technique, removes that overhead and can make forms open noticeably faster.

Settings that quietly cost time

Two default settings are well known for slowing Access down:

• Name AutoCorrect, which tracks object name changes and has a cost on every operation.
• Subdatasheets set to automatic on tables, which makes Access look for related records every time a table is read.

Turning both off is standard practice in a multi-user application.

The network itself

Access expects a fast, reliable, wired local network between each PC and the back end file. It copes poorly with:

• Wi-Fi, where brief dropouts are normal;
• VPN connections from home;
• links between offices.

These are slow, and they also put the data at risk, because a connection that drops in the middle of a write can damage the file. No amount of tuning makes an Access back end file suitable for use over a wide-area connection.

When tuning is not enough

If the application is split, indexed and sensibly designed and is still struggling, it has probably reached the limits of a file-based back end. That is the point at which moving the data to SQL Server makes sense, usually while keeping the Access front end. The server then does the searching and sends back only the results.

That move rewards the same good practice. An application that loads whole tables will be slow on SQL Server too, so the tuning described here is not wasted effort. It is the first stage of the same job.