How to Perform Compact & Repair Process on Access Database

Compact & Repair helps to improve the performance of Access database. Sometimes when we are working on access database, a serious problem might occur or you might receive an error message, at that time repair operation get failed & you should compact and repair Access database. We can perform compact & repair on access database by two ways:

“Manually & Automatically”

Manually compact and repair a database:

you can also run compact & repair database command manually. You can run this command on open database as well on closed database. Check this article in this post, I have discussed both methods.

Limitations of Compact & Repair utility:

• The compact & repair utility does not repair interface objects like forms & reports. 
• The utility is not able to recover all the data from a severely corrupted database.
• First make sure that there should be enough space on the disk for original & compacted database. Otherwise the process will not successfully get completed.
• Prevented your database from accessing and modifying when the compact & Repair utility is running.

Automatically compact and repair a database when it closes

You can also perform Compact & Repair command automatically on close access database. 

For Access Database 2007 & 2010:

1.Select Office button >> Access Options

2.Select the Current Database item on the left border

3.Check the "Compact on Close" box:

For Access 2003 or Earlier versions: 

1.Select the Tools>>Options menu option to open the Access Options form

2.On the General tab, check the "Compact on Close" box:

Limitations of Compact on Close

• The Compact on Close option can use other system resources which can delay the other user tasks. 
• The Compact on Close option is only compacted and repaired when you successfully open and close your database. 
• For multi-user databases, you must ensure that no other users are accessing the database during this process.

I always preferred to manually compacting Access database. You must weekly run Compact & Repair utility for better database performance.