About compacting and repairing an Access file

Microsoft Access combines compacting and repairing an Access file (Microsoft Access file: A database or project file. In Access 2007, database objects and data are stored in .accdb files. Earlier versions use.mdb files. An Access project file doesn't contain data, and is used to connect to a SQL Server database.) into a single process.

ShowCompacting an Access file

If you delete data or objects in an Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.), or if you delete objects in an Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.), 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 (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) — such as tables or views — that reside in the Microsoft SQL Server database (Microsoft SQL Server database: A database that consists of tables, views, indexes, stored procedures, functions, and triggers. You can connect your database to SQL Server data by using ODBC or by creating an Access project (*.adp) file.) 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 (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.), 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.

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 (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.), use Access user-level security (user-level security: When using user-level security in an Access database, a database administrator or an object's owner can grant individual users or groups of users specific permissions to tables, queries, forms, reports, and macros.) instead of file-level permissions. Otherwise, use folder permissions. For more information about the NTFS file system and folder permissions, see Windows Help.

ShowRepairing an Access file

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:

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.

ShowRepairing files after Access shuts down unexpectedly

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.

Applies to:
Access 2003