How to Compact and Repair a Database Manually in Access 2016

Access allows you to manually compact and repair a database to prevent performance and/or file corruption problems.

You can compact and repair a database either manually, or automatically whenever the database is closed.

These are the steps for compact and repairing a database manually.

It is recommended that you back up the database before compacting and repairing.

1. Open the File Menu

   With the database already open, click File in the top left corner.

2. Run Compact & Repair

   Click the Compact & Repair Database button.

   The compact and repair process runs immediately.

Multi-User Environment?

The Compact and Repair process may disrupt anyone using the database.

Therefore, you should only run Compact and Repair when you have exclusive access to the database.

If you have multiple users, you should notify them whenever you plan to run a compact and repair operation, so that they can stop using the database during that time.

Why you should Compact and Repair

Compacting a database is one thing, repairing is another. Here's an explanation of why both should be done.

Compact

As data is updated and a database's design changes, its file size grows. Sometimes Access creates hidden objects in order to perform certain tasks. These objects can often remain in the database long after they're needed.

Also, when you delete objects in Access, the disk space is not automatically reclaimed. The database file still uses the disk space that the deleted object previously used.

Over time, these factors can have a negative impact on performance. Database objects could take longer to open, queries might take longer to run, and the whole database might seem "sluggish".

Compacting the database frees up the unused space that these objects used to take up. The compact operation doesn't actually compress the data. It simply releases the unused space.

The end result of compacting a database is that it should feel (and actually be) quicker. The file size may be reduced too. The degree of these factors will depend on how heavily used the database is, and when it was last compacted.

Repair

There are some cases where a database file can become corrupted. While this can happen in a single user environment, it's much more common in a multi-user environment.

If a database file is shared over a network and multiple users work directly with the file simultaneously, that file is at risk of becoming corrupted.

When a file is corrupted, Access marks the database file as corrupted. The file can be repaired using the Compact and Repair process, although some data may be missing after the repair process has completed.

If you try to open a corrupt database file, you will be prompted to let Access automatically repair the file.