When to Compact & Repair your MS Access database?

If you are an MS Access user, you must have heard about the 'Compact & repair' utility but do you know the real purpose of this command. It is very important to understand when and why to use this command.

Why Run Compact & Repair Utility on the MS Access database?
When you add or update data in your database, the size of database file also continue to grow and at one point it becomes too large to manage. Actually when you delete a record from your database, the space that your record has occupied is not released for reuse automatically. It will continue to exist in your database as waste space until and unless you compact the database. Waste space may degrade the performance of your database. To avoid these issues you must compact your database at regular intervals.

Compact & Repair: Compact & Repair utility is used for two purposes, first it compacts your database to eliminate waste space and second it repairs your database in case it becomes corrupt. Compact utility reduce the size of your database by eliminating unused/waste space. It is the best way to reduce the size of your database. If you perform more addition, update operation on your database then you should compact your database more often.

How to Compact & repair a database?
Take a backup of your data before using compact & repair utility because during repair process, Access sometime delete the data from damaged table. So you should have a valid backup of your data.

You can compact & repair your database in two ways:
Automatically: If you want Access to compact & repair your database automatically every time you close the database then follow below steps:

1. Open your Access database.
2. Go to File-> Options-> Access Options.
3. Now click Current Database.
4. Click the Compact on Close checkbox under Application options.

Manually: You can run this utility manually on both open and close database.
Compact & repair database when it is open:

1. Go to file-> Info-> Compact & Repair Database.

Compact & repair database when it is close:

1. Start Access-> Info-> Compact & Repair Access database.
2. A dialog box named Database to Compact Form will be open, double click the database that you want to compact & repair.

Note: You won't be able to run compact & repair utility if any other user is sharing same database file.

Although compact & repair utility is a great way to compact your database but it has some limitation also. If your database becomes corrupt then repair utility will restore only queries, tables and indexes. It does not give guarantee to recover your database fully. In this case you can choose a good Access repair tool for the successful recovery of your database.