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.

 In this article, we examine the process you should follow to ensure optimal functioning of your database.

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 other 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. 

Note: This article describes the process of compacting and repairing an Access 2013 database.  The steps are the same as those used for compacting and repairing an Access 2010 database.  If you are using an earlier version of Microsoft Access, please read Compact and Repair an Access 2007 Database instead.

Difficulty: Easy

Time Required: 20 minutes (may vary depending upon the size of the database)

Here's How:

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.  If you are not familiar with backing up Microsoft Access, read Backing Up a Microsoft Access 2013 Database.
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.

What You Need:

• Microsoft Office Access 2010; or
• Microsoft Office Access 2013