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
In Access 2007 the database maintenance routines are on the Office button.
from the left-hand menu and then
select the 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.
and earlier the is option under
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.
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.
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.