Compacting and Repairing a Database

As you and the users of an application work with a database, the database grows in size. In order to maintain a high state of performance, Access defers the removal of discarded pages from the database until you explicitly compact the database file. This means that as you add data and other objects to a database and remove data and objects from the database, Access does not reclaim the disk space that the deleted objects occupied. This not only results in a very large database file, but it also ultimately degrades performance as the physical file becomes fragmented on disk. Compacting a database accomplishes these tasks:

It reclaims all the space occupied by deleted data and database objects.
It reorganizes the database file so that the pages of each table in the database are contiguous. This improves performance because as the user works with the table, the data in the table is located contiguously on the disk.
It resets counter fields so that the next value will be one more than the last undeleted counter value. If, while testing, you add many records that you delete just prior to placing the application in production, compacting the database resets all the counter values back to 1.
It re-creates the table statistics used by the Jet Engine when it executes queries, and it marks all queries so that Jet recompiles them the next time they are run. These are two very important related benefits of the compacting process. If you have added indexes to a table or if the volume of data in the table has changed dramatically, the query won't execute efficiently. This is because Jet bases the stored query plan it uses to execute the query on inaccurate information. When you compact the database, Jet updates all table statistics and the plan for each query to reflect the current state of the tables in the database.

It is a good idea to defragment the hard drive that a database is stored on before performing the compacting process. The disk defragmentation process ensures that as much contiguous disk space as possible is available for the compacted database. The compaction process is a defragmentation of the database. By defragmenting the disk drive and then the database, you increase the database performance, as well as the performance of the entire computer system.

In versions of Access prior to Access 2000, the repair process is a separate utility from the compacting process. With Access 2000, Access 2002, and Access 2003, there is no longer a separate repair process. The compact and repair processes both occur when you compact a database. When you open a database that is in need of repair, Access prompts you to compact it.

To compact a database, you can use one of three techniques:

Use commands provided in the user interface.
Click an icon you set up.
Set up the database so that Access compacts it whenever you close it.

Regardless of which method you select for the compacting procedure, the following conditions must be true:

The user performing the procedure must have the rights to open the database exclusively.
The user performing the procedure must have Modify Design permission for all tables in the database.
The database must be available so that you or the user can open it for exclusive use. This means that no other users can be using the database.
The drive or network share that the database is located on cannot be read-only.
You cannot set the file attribute of the database to read-only.
Enough disk space must be available for both the original database and the compacted version of the database. This is true even if you compact the database to a database with the same name as the original.

It is a good idea to back up a database before you attempt to compact it. It is possible for the compacting process to damage the database, such as when a power failure occurs during the compaction process. Also, you should not use the compacting process as a substitute for carefully following backup procedures. The compacting process is not always successful. Nothing is as foolproof as a fastidiously executed routine backup process.

If, at any time, Access detects that something has damaged a database, it prompts you to repair the database. This occurs when you attempt to open, compact, encrypt, or decrypt a damaged database. At other times, Access might not detect the damage. Instead, you might suspect that damage has occurred because the database behaves unpredictably. If you suspect damage, you should first back up and then perform the compacting process, using one of the methods covered in this hour.

Using the User Interface to Compact a Database

Access provides a fairly straightforward user interface for the compacting operation. To compact a currently open database, choose Tools | Database Utilities | Compact and Repair Database. Access closes the database, compacts it, and then reopens it.

To compact a database other than the currently open database, follow these steps:

Close the open database.
Choose Tools | Database Utilities | Compact and Repair Database. The Database to Compact From dialog box appears, as shown in Figure 20.2.

Figure 20.2. The Database to Compact From dialog box.
Select the database you want to compact and click Compact. The Compact Database Into dialog box appears, as shown in Figure 20.3.

Figure 20.3. The Compact Database Into dialog box.
Select a name for the compacted database. You can make it the same name as the original database name, or you can create a new name. (If you are compacting a database to the same name, make sure that it is backed up.) Click Save.
If you select the same name for the compacted database as for the original, Access prompts you to replace the existing file. Click Yes.

Using a Shortcut to Compact a Database

To give users a very simple way to compact a database, you can create an icon that performs the compacting process. You accomplish this by using the /Compact command-line option, which compacts the database without ever opening it. The shortcut looks like this:

c:\MSOffice\Access\Msaccess.EXE c:\Databases\TimeAndBilling.MDB /Compact

This command compacts a database named TimeAndBilling in a folder called c:\Databases. You can follow this command with a space and the name of a destination database if you do not want Access to overwrite the current database with the compacted version. If you do not include a path for the destination database, Access places it in the My Documents folder by default. You can have Access automatically create the shortcut for you, using the Setup Wizard that ships with Office 2003 Developer. To create a shortcut, follow these steps:

Open the folder where you have installed an application.
Right-click the application (MDB) icon for a database and choose Create Shortcut from the context menu. A shortcut appears on the desktop.
Right-click the shortcut and choose Properties from the context menu. The Properties dialog box appears.
Click the Shortcut tab.
Modify the shortcut so that it has the following syntax:

MSACCESS.EXE filename /Compact

Compacting Whenever a Database Closes

Using the environmental setting Compact on Close, you can designate that Access should compact specific databases whenever the user closes them. Access compacts a database upon close only if it determines that the compacting process will reduce the size by at least 256KB. To set the Compact on Close environmental setting, follow these steps:

Open the database that you want to affect and select Tools | Options.
Click the General tab of the Options dialog box.
Select the Compact on Close check box.

Although you set the Compact on Close setting by selecting Tools | Options, this setting applies only to the database that is open when you select the option. This allows you to selectively designate which databases Access compacts when the user closes them.

Remember that when you use the Compact on Close option, the database must meet all the conditions ordinarily required for Access to compact a database. For example, if other users are in the database when someone tries to close it, the user trying to close the database receives an error.