Compact and repair an Access database

 Jan 06, 2015

Databases can get big. Very big. This can be because they contain a large amount of data, but it can also be because of wasted space taken up by deleted data. When records are deleted, the space they occupied is not reclaimed. The remaining data can become fragmented and even corrupted. Microsoft Access has a utility that can compact and repair the database to fix these issues. It is found on the Database Tools tab on the Ribbon as well as under the File tab. It is a good idea to use this feature to make the database as lean and efficient as possible. Before using the tool, a backup copy of the database should be made. Once this is done, the procedure is simple:
  1. Get anyone who has the database open to close it.
  2. Go to the ‘Database Tools’ tab on the Ribbon.
  3. Click the ‘Compact and Repair Database’ button.
The database will close, do its compacting procedure and then re-open. The amount of time that it takes for the compact and repair to occur will depend on the size of the database and the amount of work that needs to be done. It could happen in a split second or it could take 20 minutes. In addition to the manual utility, there is an option that can be set to do a compact and repair every time the database is closed. To enable this feature is very easy:
  1. Go to the ‘File’ tab and choose ‘Options.’
  2. Click the ‘Current Database’ category on the left.
  3. Tick the ‘Compact on Close’ check box.
So now you can keep your databases running like clockwork by performing this simple maintenance task!

How do your Excel skills stack up?   

Test Now  

About the Author:

Ben Kirk  

With over 16 years of experience working as a Desktop Applications specialist for a number of large education services providers, Ben is one of New Horizons most skilled and dynamic instructors. With his Advanced Diploma of Business Skills alongside his practical experience and expertise, Ben is able to provide insight and guidance to students at all skill levels across the entire Microsoft Office suite.

Read full bio