How To Compact An Access Database

As one works with a database, adding and removing records, the .mdb file that Access uses to store the database grows and grows. It grows even if records, tables and relationships are deleted because unfortunately, filling in the "holes" caused by deleting elements is not condusive to maintaining the speed of operation of the database. (A complete discussion of this phenomenon is beyond the scope of this discussion.)

At any rate, every now and then, one needs to stop and "compact" the database to re-optimize it by putting the data back into a quickly accessible form that has no "holes" in the database file. This is process is analogous to running "speed disk" on one's hard drive to improve its performance and to free up space. Compacting a database can reduce the size of the .mdb file by as much as a factor of two or three! Access provides a utility to do this under the Tools/Database Utilities/Compact and Repair Database... menu. Follow these steps:

  1. Close any open database in Access by clicking the "X" in the upper right corner of the Database Explorer window inside of Access. Do not close Access itself! Compacting will not work if a database is open.
  2. Go to Tools/Database Utilities/Compact and Repair Database... A window titled "Database to Compact From" will appear.
  3. Browse for and highlight the database you wish to compact.
  4. Click the "Compact" button in the lower right corner of the window.
  5. Access will come back with a window titled "Compact Database Into" where the "Filename" textfield filled with a name like "db1.mdb". Change that name to one to which you would like to save your compacted database. Do not overwrite your original database!
  6. Click on the "Save" button.
  7. Exit from Access.

If you want to preserve the filename of your database:

  1. Rename your original database to some sort of backup name. For instance, if your database is called "myDB.mdb" then rename it to something like "myDB.old1.mdb".
  2. Do not delete the original database!
  3. Rename your compacted database to the original name.