Compact and repair an Access database

Microsoft Access databases have always had an unfortunate tendency to get larger and larger in everyday use. Part of this growth may be because the users are adding new data to the system but mostly the growth comes about because of the way that Access was designed. When the user changes something in the database the new version probably won't fit into the same space as the original and has to be added to the end of the file. The original data is left in place but Access ignores it and won't reuse that space.

The database administrator (or whoever is responsible for the maintenance of the database) must do some simple maintenance work to keep the situation under control.

In Access 2007 the database maintenance routines are on the Office button. Click Manage from the left-hand menu and then select the Compact and Repair Database option. Access will rearrange the mdb file to reclaim the wasted space. There are no progress messages, the database user interface will just open as usual when the process is complete.

In Access 2000 and earlier the Compact Database is option under Database Utilities on the Tools menu.

Problems

There are a number of reasons why the compaction procedure might fail to run:

• The compaction process moves data around in the mdb file and it cannot do this if any other user is working with the database.
• The process has to write data to the database and cannot do this if the file is read-only. It might be on a network drive where you do not have permission to write or modify files or it might be a file that has retained its read-only status after having been copied from a CD.
• The compaction process requires Open, Run, and OpenExclusive privileges on the database and these might have been blocked by Access' own security settings. Ask the database owner to compact the database or upgrade your permissions so that you can compact the database yourself.
• At worst the compaction process might need enough space for three complete copies of the database. It might be working with the original file, the new file, and a large amoount of data in temporary working space. This is a rare problem with modern hard drives but might crop up if the network administrator has only given you a small quota of storage space.

Warning

You must make a backup copy of the database before compacting it. The compaction process is generally reliable but if it does fail then the database might be corrupted. A good recent backup is the easiest way to recover.

Use the Backup Database option on the Database Management menu to make a backup copy before you compact. Access 2007 will offer to create a file with a name based on today's date. If you're backing up myDatabase.mdb on December 25th 2012 then the backup will be named myDatabase_2012-12-25.mdb. The use of the yyyy-mm-dd format makes it easy to sort the backup files into chronological order.