Which of the following is not true about compacting and repairing a database?

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 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 10.

Top of Page

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.

1. Select File > Options.

2. In the Access Options dialog box, select Current Database.

3. Under Application Options, select the Compact on Close check box.

4. Select OK.

5. Close and reopen the database for the option to take effect.

Top of Page

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.

Top of Page

Manually compact and repair a database that is not open

Use this procedure when you can't directly open an Access database.

1. Make sure no other users are currently using the database file.

2. Start Access.

3. In Access 2013, Access 2016, and Access 2019:

   1. On the templates page, double-click Blank Database.

   2. Select File > Close.

4. Select Database Tools > Compact and Repair Database.

5. 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.

Top of Page

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.

Top of Page

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.

Top of Page

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 Memo 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.