Compact and repair an Access database

Access databases have an unfortunate tendency to bloat. They grow much larger than would appear to be necessary for the amount of data that they are holding. This is due to a basic design feature of Microsoft Access and you need to do some regular maintenance work to keep the situation under control.

The problem is that Access is not very good at reusing space in the mdb file and every change that you make is saved in a new part of the database. This applies whether you are developing a database or just updating the data; Access will extend the database to hold this new information rather than trying to use the existing space again. Every change increases the size of the Access database and this bloat reduces its performance.

The Compact Database option under Database Utilities on the Tools menu recovers this wasted space. This option was changed to Compact and Repair in Access 2000.

You may be surprised by the amount of space recovered; we have seen a database grow to 15 Mb in three months of use and reduce to 320 kb after compaction.

Problems

A number of problems may stop you being able to run the compaction procedure:

• Somebody else is using the database. The compaction process moves data around in the mdb file so it cannot be allowed to run if any other user is working with that data.
• The database is read-only. Perhaps it is on a network drive where you do not have permission to write or modify files. The most annoying reason is when Windows decides that the file on your hard disk must be read-only because you have just copied it up from a CD. Right-click on the mdb file in Windows Explorer, select Properties, then clear the Read only tick box.
• You are not the owner of the database or you do not have Open, Run, and OpenExclusive privileges. You will have to find the administrator who does own the database and ask them to either compact the database or upgrade your permissions so that you can compact the database.
• The final problem is a rare one with modern hard disks. Access does need a lot of spare disk space to be able to run the compaction. It needs to be sure of having enough space for up to three copies of the database;
  • the original copy
  • the new copy
  • some temporary working space whilst it's moving data

Warning

Make sure you have a good backup copy of the database before starting the compaction. The process is generally reliable but on rare occasions the compaction can fail and leave you with a corrupt database. The only guaranteed cure for this is a recent good backup.