How to Compact and Repair a Database Automatically in Access 2016

Access allows you to compact and repair a database automatcially whenever the database is closed. This can 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 automatically whenever the database is closed.

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

In this case, you would need to back up the database before you close it.

1. Open the File Menu

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

2. Open the Options Menu

   Click Options in the left menu.

3. Change the Settings

   The Access Options dialog box appears.

   Click Current Database in the left menu.

   Then check Compact on Close under Application Options.

   Click OK.

4. Close and Reopen the Database

   A dialog box is displayed explaining that you must close and reopen the database for the changes to take effect.

   Click OK, then close and reopen the database.

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.