About compacting and repairing an Access file

If you delete data or objects in an Access database, or if you delete objects in an Access project, the file can become fragmented and use disk space inefficiently. Compacting the Access file makes a copy of the file and rearranges how the file is stored on your disk. Compacting a previous-version Access database won't convert it to Access 2002 - 2003 format.

Compacting optimizes the performance of both Access databases and Access projects. However, in an Access project, compacting does not affect database objects— such as tables or views— that reside in the Microsoft SQL Server database instead of in the Access project itself.

Compacting has no effect on autonumbering in an Access project. However, in an Access database, if you have deleted records from the end of a table that has an AutoNumber field, compacting the database resets the AutoNumber value; the AutoNumber value of the next record you add will be one greater than the AutoNumber value of the last undeleted record in the table.

Caution  When you compact a Microsoft Access file located on a volume that uses the NTFS file system, Access removes the existing file and replaces it with the compacted file. It then applies the default file permissions to the new file. If the file is an Access database, use Access user-level security instead of file-level permissions. Otherwise, use folder permissions. For more information about the NTFS file system and folder permissions, see Windows Help.

In most cases, Microsoft Access detects whether an Access file is damaged when you try to open it and gives you the option to repair it at that time. If the current Access file contains a reference to another Access file that is corrupt, Access does not attempt to repair the other file. In some situations, Access may not detect that a file is damaged. If an Access file behaves unpredictably, compact and repair it.

Access can repair:

• Corruption in a table in an Access database.

• Missing information about the structure of an Access file's Visual Basic for Applications (VBA) project.

• Corruption in a form, report, or module.

• Missing information that Access needs to open a particular form, report, or module.

To help prevent corruption of Access files, follow these guidelines:

• Compact and repair Access files regularly. You can specify that Access compact a file automatically when you close it.

• Back up your Access files regularly.

• Avoid quitting Access unexpectedly. For example, don't quit Access suddenly by turning off your computer.

• If you are experiencing network problems, avoid using a shared Access database located on a network server until the problems are resolved. If possible, move the Access database to a computer where you can access it locally instead of over the network.

If a serious problem occurs and causes Microsoft Access to shut down, Access restarts, creates a copy of the file that was open, and names it filename_Backup.mdb or filename_Backup.adp, where filename is the name of the original file. If filename_Backup.mdb or filename_Backup.adp already exists, Access asks you to specify the name of the file. Access then attempts to compact and repair the original file.