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.

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. 

The steps for compacting and repairing an Access 2013 database is the same as those used for compacting and repairing an Access 2010 database. If you are using an earlier version of Microsoft Access, please try a different tutorial instead.

This easy task should only take 20 minutes, depending on the size of your database.

How to Compact and Repair an Access Database

  1. 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.
  2. 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.
  3. In the Access Ribbon, navigate to the Database Tools pane.
  4. Click the "Compact and Repair Database" button in the Tools section of the pane.
  5. Access will present the "Database to Compact From" dialog box. Navigate to the database you wish to compact and repair and then click the Compact button.
  6. Provide a new name for the compacted database in the "Compact Database Into" dialog box, then click the Save button.
  7. 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.)

    Tips

    1. 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.
    2. 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.