Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
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 Access 2007.option under on the menu recovers this wasted space. This option was changed to in Access 2000 and changed again in
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.
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 , then clear the 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
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.