Compacting A Database (Using The Workbench)
Applies to Access 97, 2000, 2002, 2003
            

If you need a little background on compacting, click here

The Workbench Compacting Process

The biggest issue that you face when trying to compact a busy database regularly, is trying to get everyone out of the database before you run the process. To help you with this process, the Access workbench has two options. Firstly there is the Lock checkbox on the main screen that will stop any new person from logging into the database (whilst the lock is on).

The second option is the Compact checkbox (as shown on the left hand side of Figure 1).  If you select this checkbox, this tells the workbench to compact your database the next time everyone is out the database.  To find this out, the workbench needs to display a list of users currently in the database using the Who's On button. If this resolves that no one is using the database, the workbench will immediately Compact your database and save the compacted database to the same filename as before. It does this using a Microsoft tool that is installed for all MS Access installations.

Figure 1 - The Compact checkbox informs the workbench to Compact the database next time the Who's On list is empty.

How To Compact The Database

Select the Compact checkbox and press the Who's On button.

Figure 2 - The Compact checkbox is selected and the database will be compacted when everyone logs out.

If you know that there are users in your database, select the compact checkbox and wait till they all log out (at your request).  Then click the Who's On button.  Alternatively wait until the Who's List is refreshed at the next regular interval as defined in the Options form (see Figure 3).  (Press the Options Button to see these options).

Figure 3 - The Options form lets you set the number of minutes between refreshing the Who's On list.

Note: You can also compact the database using the menu Database ~ Compact (when no one is using the database).  This will also try the Who's on button for you as part of the same process. If the database is in use, the Compact checkbox will remain selected.

How To Auto-Compact The Database

If you use the favorites tab regularly, you will find that it is a relatively easy thing to add the minimum size of database that you would consider as appropriate for compacting the database. To do this, select the database in the favorites and choose the Edit button (as show in Figure 4). Now if you choose a database and the size of the database is greater than that amount, the Compact check box will be selected automatically for you. After this, all you have to do is to wait until everyone has logged out including yourself and the database will compact automatically for you.

Figure 4 - In Favorites you can set the Auto Compact size for a database using the Edit button

To Repair A Database

Choose the menu  Database ~ Repair (irrespective of warnings) option and this will repair the database. This is a good idea for Access 97 which doesn't repair the database at the same time as it compacts it.

Compacting And Repair Database Background Material

Why Compact A Database 

To maintain a high state of performance, Microsoft Access defers the removal of discarded pages until you shut down the database and compact the discarded pages. This design keeps the interactive performance of your database high at the expense of recoverable disk space. Compacting a database copies all data from one database into another and organizes the data in the resulting database contiguously so that disk space can be recovered.  This is the reasons why a compacted database is smaller, faster and (if undertaken on a regular basis), more stable as well.  In other words, Compacting is an essential task for a Database Administrator.

Compacting your database

The best and easiest way to compact an Access database is to select the menu { Tools ... Database ... Compact Database }. If you are the only one in the database, this will close it down, compact and repair it and open it up again.  This applies to Access 2000, 2002 and 97.  The 97 compacting process does not repair the database and you will need to repeat the steps to repair the database as well.

Tip: If you have a front end and backend configuration for your Access application, do not forget to compact the backend database at regular intervals as well.

Why Do You Need To Repair a Database

If your Microsoft Jet database is damaged, close the database, then point to Database Utilities on the Tools menu and choose (Compact and ) Repair Database. The RepairDatabase method checks all pages in the database for correct linkage, validates all system tables, and validates all indexes. Because the RepairDatabase method can't fix all possible forms of database corruption, you should back up your database files regularly to avoid unrecoverable data loss. This kind of corruption can occur when the system isn't shut down normally (such as during a power failure)

Are You Having Trouble Compacting A Corrupt Database
Try this page

Find Out More About Keeping An Access Database In Tip Top Condition
Access 2000 http://support.microsoft.com/Default.aspx?KBID=300216#8
Access 2002 http://support.microsoft.com/kb/303528/EN-US/#8
Access 97 http://support.microsoft.com/kb/303519/EN-US/#8

What to do when you cannot uncorrupt a database Read More

Other Access Workbench Topics
Home Page | Starting Up | Stop New Users | Install | Internal Logging | FAQ Page  | Orders

The Access Workbench Help File  - Compacting A Database