Tuesday, April 21, 2015

Power of Compact & Repair

In this blog we will see, how “Compact and Repair” Command can help to prevent and correct the problems that sometimes affect a database file.
Why we use compact and repair command?
When we create a database then, it occupies a certain amount of space in the memory. In this image suppose blue circle is the full memory and red part shows the occupied memory by database.

When we remove an object from the database then some amount of memory occupied by the database will be free and when we add any new object in the database then, the size of database file will be increase.Sometimes the empty space cannot be accessed by the computer as shown in below image.

In other words you can say the other applications cannot use this memory space for their uses.To overcome from this situation we compact the database. As you can see, space occupied by database is now lesser than previously.
After the execution of this command the memory will be free for other resources.
Before using this command, do these important things: If you are going to use compact and repair command then you have to take care of the following things.
  1. Take Backup: During repair process, MS Access may truncate some damaged data from the table. Sometimes it is possible to recover this data from the updated backup. If you use the database on the daily basis then, you should take the backup of database according to your schedule. You can go on the File tab, click Save & Publish then under Advanced option you will get the option for Back Up Database.
  2. Automatically: If your database is shared by other users on the network then you should set your database to automatically compact and repair.For this first go on File tab then, click on the Options. In the Access Options dialog box, click Current Database. Under the Application Options, select Compact on Close check box.
  3. Need to know about system recovery errors table: Any objects that cannot be recovered in Access database repair process are noted in a table named MSysCompactErrors. We can see the datasheet view of this table and determine which object you want to import from the database backup into required database.
  4.  Permission to run this command: If you are using the earlier version of MS Access then, you might be unable to compact and repair the database on your own. If you need the sufficient permission for compact and repair your database. You can contact your work-group administrator for assistance.

From the second point it is clear that, we can also use the command Compact & Repair automatically. Now we will see how it works manually. you can run this command when your database is open or close. A shortcut for  compact & repair command can also be created for a particular database.
  • If database is open: In the following figure you can see the option of Compact & Repair. If database is open then you can directly use this option.

  • If database is close:Start Access but do not open your database. Go to the Info option then, click on the Compact & Repair. After this you will see the following dialog box and you can select the database according to your choice.


  1. I read so many blogs on compact and repair but you have brilliantly described the power of compact and repair. Thanks for the information!


Related Posts Plugin for WordPress, Blogger...