Database files can grow quickly as you use them, sometimes impeding performance. They can also occasionally become corrupt or damaged. You can use the Compact and Repair Database command to prevent or fix these problems. The compact process does not compress your data — it makes your database file smaller by eliminating unused space. The Compact and Repair Database command can also help improve performance of your database.
Tip Splitting a database can help prevent the corruption of database files and limit the loss of data by keeping the data in a separate file that users do not access directly.
Ways to compact and repair a database
There are several approaches to compacting and repairing a database. A common practice is to automatically compact and repair a database when it closes. In addition, you can manually run the Compact and Repair Database command, when you have a database open, and on a database that is not open.
Before you begin
Take the following actions before you begin a compact and repair operation:
-
Make a backup of the database During the repair process, Access may truncate some data from tables that are damaged. It is sometimes possible to recover this data from a backup. In addition to your regular backup strategy, you should make a backup immediately before you use the Compact and Repair Database command. For more information, see Protect your data with backup and restore processes.
-
Get exclusive access to the database A compact and repair operation requires exclusive access to the database file, because the operation may disrupt other users. You should notify other users when you plan to run a compact and repair operation, so that they can avoid using the database during that time. For more information, see Open an existing Access database.
Tell users how long they must avoid using the database. If you run compact and repair operations on a regular basis, keep a record of how long it takes. You can then make more precise estimates that give other users guidance on how long they should avoid using the database.
-
Get sufficient file permission to the database If you do not have sufficient permissions and you need to compact and repair a database, contact your system administrator for assistance. For more information, see Changes to file sharing over a network in Windows.
Automatically compact and repair a database when it closes
You can select the Compact on Close database option if you want to automatically compact and repair a database when it closes. Setting this option affects only the database that is currently open. Set this option separately for each database that you want to automatically compact and repair. In multiuser databases, you might not want to set this option, because it can momentarily disrupt database availability.
-
Select File > Options.
-
In the Access Options dialog box, select Current Database.
-
Under Application Options, select the Compact on Close check box.
-
Select OK.
-
Close and reopen the database for the option to take effect.
Manually compact and repair a database that you have open
-
Select File > Info > Compact & Repair Database.
Access creates a copy of the compacted and repaired database in the same location.
Manually compact and repair a database that is not open
Use this procedure when you can't directly open an Access database.
-
Make sure no other users are currently using the database file.
-
Start Access.
-
On the templates page, double-click Blank Database.
-
Select File > Close.
-
Select Database Tools > Compact and Repair Database.
-
In the Database to Compact From dialog box, navigate to and double-click the database that you want to compact and repair.
Access creates a copy of the compacted and repaired database in the same location.
Compact and repair a corrupted database when Access prompts you
When you try to open a database file that is corrupted, if you are prompted by Access to compact and repair the database, select Yes. Two things can happen:
-
If Access completely repairs a corrupted file, it displays a message stating that the repair was successful and that you should check the database contents to be sure that everything is as it should be.
-
If Access is only partially successful, it keeps track of database objects that it could not repair in a system table named MSysCompactErrors. Access opens the MSysCompactErrors table in datasheet view. If you have a previous backup before the database was corrupted, you can use the MSysCompactErrors table to decide which objects to import into your repaired database. To reveal system tables, right click the Navigation title bar, and then in the Navigation Options dialog box, select Show System Objects.
Why you should compact and repair a database
This overview explains how using the Compact and Repair Database command can help prevent and correct the following problems that sometimes affect a database: files growing larger with use and files becoming corrupted.
Database files grow with use
As you add and update data and change its design, a database file becomes larger. Some of this growth comes from new data, but some comes from other sources:
-
Access creates temporary, hidden objects to accomplish various tasks. Sometimes, these temporary objects remain in your database after Access no longer needs them.
-
When you delete a database object, the disk space that the object occupied is not automatically reclaimed — the database file still uses that disk space, even though the object is deleted.
As your database file fills up with the remains of temporary and deleted objects, its performance can degrade. Objects may open more slowly, queries may take longer than normal to run, and typical operations generally seem to take longer.
Database files can become corrupted
In particular circumstances, a database file can become corrupted. If a database file is shared over a network and multiple users work directly with the file simultaneously, that file has a small risk of becoming corrupted. The risk of corruption is somewhat greater if users frequently edit data in Long Text fields, and the risk grows over time. You can mitigate this risk by using the Compact and Repair Database command.
Often, this type of corruption results from a problem with a Visual Basic for Applications (VBA) module and does not pose a risk of data loss. However, this type of corruption does pose a risk of database design damage, such as lost VBA code or unusable forms.
Rarely, database file corruption does result in data loss. Usually, this loss is limited to the last action of one user; that is, a single change to data. When a user starts to change data and the change is interrupted — for example, because of network service loss — Access marks the database file as corrupted. The file can be repaired, but some data may be missing after the repair is complete.
