What Does It Mean to Compact My Access Database?

Access databases are designed to grow dynamically as data is added.  Access will also create working space to manipulate data.  However, while it will grow dynamically, it does not shrink automatically when records are deleted or when it's data maniulation is done. 

What's more, as data is added and deleted, the database can become fragmented (much like a hard disk is fragmented), and records can be stored out of primary key order.  All of this can degrade the performance of your database, especially as it gets larger.

Compacting the database corrects these problems.

But before I get to that, it's important to understand exactly how compacting works because and existing file is not actually compacted.  Here's what actually happens:

1. Access creates a new, blank database called db1.mdb (Access 2003-) or database1.accdb (Access 2007+).
2. It writes the data in the original database to the new database, doing several things in the process:
3. When the process completes successfully, it deletes the original database.
4. Lastly, it renames the new database to the original database name.

This process does a number of things that improves the performance of your database.

First of all, the database is "defragmented", that is, it rearranges how the file is stored on the disk.  This is different than a disk defrag, but the effect is much the same: putting data into contiguous blocks.  In this process, empty space is reclaimed, usually leaving the database file size smaller.  This is the most common reason given for compacting, but perhaps not the most important.

Secondly, it rearranges the order of the records by writing them in primary key order.  This is the equivalent of a Clustered Index in other database implementation and makes the read-ahead capabilities of the database engine more efficient.

Thirdly, it rebuilds the table statistics which are used by the query optimizer to optimize queries.  It also forces a re-compile of your saved and embedded queries based on these new stastics.

Lastly, since technically it's called Compact and Repair, it will fix any page corruptions caused by hardware or network problems.

There are some limitations, however.  Since Compacting writes the database to a new file, there must be enough hard drive space to hold both databases.  You also can't compact a database is anyone else is in it.  You must have exclusive access (although it doesn't have to opened in "exclusive" mode.)  You can compact the current database (that is, the one you have open) through the Access User Interface, but not in code.  (I'll discuss that in a later post.)

So, how exactly do you compact a database?  There are several ways, both manual and programmatic, and I'll discuss that in my next post: How Do I Compact an Access Database?.

.