How to Run the Compact & Repair tool to Optimize Database Efficiency

You may have a few tens of hundreds of thousands of records while creating an Access database file (.accdb or .mdb). But the database file may soon approach the 2GB limit degrading performance. Also, a large database file is prone to getting corrupt or damaged. Running the Compact and Repair tool can help fix these problems: it improves database efficiency and fixes a corrupted database. Let's discuss these benefits and others in detail.

Benefits of Compacting and Repairing an Access Database

The Compact and Repair tool helps clear storage space, eliminates temporary data, and fixes corruption errors to help improve the efficiency of an Access database. Let's explore all these benefits in detail:

Frees up Storage Space

Running the Compacting and Repair tool helps compress unused space.

Let's say you got a table with three records in it. If you delete one of the records, there will be a gap. That's because Microsoft Access marks a record as deleted but doesn't reclaim the space, and the data still sits there. When you add your next record, new space gets added to the end of the table. The more you add and delete records, the more gap is created, and MS Access is not good with re-using space.

For example, if you add 200 MB of records and delete 100 records, then add another 100 MB of records, you'll have 300 MB of storage space wasted in your database. So, compacting an Access database is important as it gets rid of all that space from all those deleted records, and you'll have a fresh database file with no empty spaces or gaps. This will reduce the size of your database and makes it efficient. And, of course, a small database ensures faster performance both on a computer and on a network share.

Note: You must also consider splitting your database if it is approaching the 2GB limit. Split your database with multiple users in a front-end and a back-end database.

Gets Rid of Junk

Microsoft Access doesn't clean up junk. There's an index file and temporary objects like tables that Access doesn't clean up itself automatically. So if you don't compact your database frequently, it keeps getting bigger, and performance keeps getting lower. However, compacting and repairing a database once in a while can do away with the junk.

Fixes Database Corruption

The Repair part in the 'Compact and Repair' tool can sometimes fix small problems before they get bigger. For instance, long text fields can't take more than 255 characters, resulting in database corruption. Running the Compact and Repair Database command periodically ensures to fix the long text field and other issues that could damage or corrupt a database.

Tip! If the 'Compact and Repair' utility fails to fix a corrupted database, using an Access database repair tool can save time troubleshooting and restoring the database to its original form.

How Often Should I Compact and Repair an Access Database?

You should compact and repair depending on how often you use your database. If you are using the database on your own daily, compacting it every week would be sufficient. If you got a database on the network that multiple users access, the database could easily grow in size. So, consider compacting the database regularly.

Steps to Compact and Repair a Database

You can compact and repair a database using different approaches. One approach is to run the compact and repair tool when the database closes automatically. Or, you can manually compact and repair an open database or a database that won't open.

Note: Avoid selecting the option to compact a database when it closes for multi-user databases, as it can disrupt database availability and slows you down. Also, the 'Compact on Close' option could make you lose data if it is performed on a database marked as suspect. However, the data may be recoverable before the database is compacted.  

Before You Proceed

• Ensure to make a backup first before your compact and repair a .accdb or .mdb file.
• You must have exclusive access to your database to prevent other users from interfering with the compact and repair process.
• Ensure you have sufficient permissions to access ACCDB/MDB database file. Also, you must have permission to compact and repair a database.

Manually Compact and Repair a Database

The steps to compact and repair an Access database vary depending on whether a database is open or not. For a database that is not open, follow these steps to compact and repair it:

• Launch your MS Access application, go to File, then click Info.
• From the 'Info' screen, choose Compact & Repair Database.

A copy of the compacted and repaired database will get saved in the same location as the original DB.

To compact and repair a DB that is not open, perform these steps:

• From the templates page in MS Access program, double-click the Blank Database.

• Click on File, then click Close.
• Click the Database Tools tab, and click on the Compact and Repair Database.

• From the 'Database to Compact From' window, search the database to be compacted and repaired. Press the Compact button to continue.

You will find a copy of the compacted and repaired database in the location where the original database is stored.

Was this article helpful?

YES0

NO0

About The Author

Charanjeet Kaur

Technical writer with over 7 years of experience