How Do I Compact an Access Database?

In my previous post (What Does It Mean to Compact My Access Database?) I talked about what compacting an Access database is.  In this post, I'm going to discuss how to do it.  As with most things in Access, there are a number of ways, and the specifics change from version to version.

Compact On Close
I'm starting with Compact On Close to get it out of the way.  Few experienced Access developers recommend this option. 

Steps by version:
Access 2003: Tools > Options > General > Compact On Close (checkbox)
Access 2007: Office Button > Access Options > Current Database > Compact On Close (checkbox)
Access 2010: File > Options > Current Database > Compact On Close (checkbox)

While regular compacting can be good for performance, it's rare that a database needs to be compacted *every* time it closes.  If it does, there are steps you can take to reduce that necessity (I'll address that in a later post.) But the real problem with Compact on Close is the risk of database corruption.  Anytime you compact your database, there is a small risk of corrupting it.  When you do it every time you close the database, the changes increase simply due to its frequency. This is why most of us discourage the use of Compact On Close.

Compacting the Current Database in the UI
In Access, you can compact the current database from the Menu/Ribbon as long as you are the only one that has it open.

Steps by version:
Access 2003: Tools > Database Utilities > Compact and Repair Database...
Access 2007: Office Button> Manage > Compact and Repair Database
Access 2010: Database Tools tab > Compact and Repair Database

As I said in my last post, the compact process writes the records to a new database, deletes the old one, and renames the new one to the old name.  This is where the risk of corruption comes into it.  If something happens during the write process -- say, by a network interruption -- the new database can be corrupted and when the old one is deleted, you've lost your only good copy.

I have had this happen to me.  A database that's corrupted during compacting is, in my experience, corrupted beyond repair.  But I have to say that I believe later Access versions are better at ensuring corruption doesn't happen.  Still, there's always a risk.  That's why it's a good idea to make a copy of your database before compacting it.
 
Because network glitches can cause corruption, it is sometimes advisable (and faster) to copy a network database to a local drive, compact it, then copy it back up to the network.  This has the added benefit of preserving a the original while you compact it.
 

Compact Another Database in the Access UI

You can also compact a database that is not open (that is, the current database) in Access.  To do this, you must have Access open, but have no database open.

Steps by version:
*With No database open in Access*
Access 2003: Tools > Database Utilities > Compact and Repair Database...
Access 2007: Office Button> Manage > Compact and Repair Database
Access 2010: Database Tools tab > Compact and Repair Database

The steps are nearly the same as compacting the current database, but it will give you two dialog boxes, one (Database to Compact From) asking for the name of the file to be compacted, and a second (Compact Database Into) asking for the name you want to give to the new database.  Using this method, you *cannot* give it the same name as the original, so it safeguards your original database.  It also means, however, that you'll have to do the renaming manually.


Using Code

There are several ways to compact a database in code, both the current database and an external database.  There's enough here to fill a post of its own, so that's will I'll address next in How Can I Compact my Access Database Less Often?

.