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.

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.