How to Compact and Repair an Access Database

Helpful tips for use with Microsoft Access 2010 and 2013 databases

Woman looking at computer screen

Hero Images / Getty Images

Over time, Microsoft Access databases grow in size and unnecessarily use disk space. Additionally, repeated modifications to the database file may result in data corruption. This risk increases for databases shared by multiple users over a network.

Therefore, it's a good idea to periodically run the compact and repair database tool to ensure the consistency of your data. You may also be prompted by Microsoft Access to perform a database repair if the database engine encounters errors within a file.

Instructions in this article apply to Access for Office 365, Access 2019, Access 2016, Access 2013, and Access 2010.

Periodically compacting and repairing Access databases is necessary for two reasons.

First, Access database files grow in size over time. Some of this growth may be due to new data added to the database, but another growth is from temporary objects created by the database and unused space from deleted objects. Compacting the database reclaims this space.

Second, database files may become corrupted, particularly those files that are accessed by multiple users over a shared network connection. Repairing the database corrects database corruption issues allowing continued use while preserving the integrity of the database. 

How to Compact and Repair an Access Database

Before you begin, ensure that you have a current database backup. Compact and repair is a very intrusive database operation and has the potential to cause database failure. The backup will be instrumental if this occurs.

If the database is located in a shared folder, be sure to instruct other users to close the database before proceeding. You must be the only user with the database open in order to run the tool.

  1. Select File and choose Close if you have a database open in the Access window.

  2. Select the Database Tools tab.

    Screenshot of Database Tools in Access
  3. Select Compact and Repair Database in the Tools group.

    Screenshot of Compact and Repair Database in Access
  4. The Database to Compact From dialog box will open. Navigate to the database you wish to compact and repair and then select Compact.

    Screenshot of Database to Compact From
  5. Provide a new name for the compacted database in the Compact Database Into dialog box, then select the Save button.

    Screenshot of Compact Database Info Save button
  6. After verifying that the compacted database works properly, delete the original database and rename the compacted database with the original database's name. (This step is optional.)

Remember that compact and repair creates a new database file. Therefore, any NTFS file permissions you applied to the original database will not apply to the compacted database. It's best to use user-level security instead of NTFS permissions for this reason.

It's not a bad idea to schedule both backups and compact/repair operations to occur on a regular basis. This is an excellent activity to schedule into your database administration maintenance plans.