With continuous usage and data storage, Access database files become large, leading to performance issues and corruption. To deal with such issues, Microsoft Access comes with an in-built utility, called Compact and Repair Database. The utility helps reduce the database file size by removing the unused space, this improving the database performance. It also helps fix corruption issues in the database.
When MS Access detects corruption in database, it automatically prompts you to compact and repair the database. However, you can also manually use the Compact and Repair utility.
In this post, we will discuss different ways to use the MS Access Compact and Repair utility.
5 Different Ways to Use Compact and Repair
Following are some ways to use the Compact and Repair utility in MS Access to compact a large database or repair a corrupt database:
Before You Begin
Ensure the following before using the Compact and Repair tool.
-
Create a backup of your Access database file before running the Compact and Repair utility.
-
Check if you have necessary permissions to access the database and use the Compact and Repair tool.
1. Run Compact and Repair Automatically
If you want MS Access to automatically run the "Compact and Repair” on a database file when it closes, enable the “Compact on Close” database option. This option applies only to the database which is currently open. You have to set the option individually for each database you want to compact and repair automatically. Here are the steps:
-
Select File and then click Options.
-
In the Access Options dialog box, click Current Database.
-
Under Application Options, choose the Compact on Close checkbox and click OK.
-
Close and reopen the database to apply changes.
2. Manually Run Compact and Repair on Open Database
You can use the “Compact and Repair” tool on closed databases or open databases. Follow these steps to run the utility on an open database:
-
Open Microsoft Access.
-
Go to File > Info > Compact & Repair Database.
After the process is complete, a copy of the compacted and repaired database is created at the same location.
3. Manually using the Compact and Repair Utility on Closed Database
If you are unable to open the corrupt database, then follow the below steps to use the Compact and Repair feature on the closed database:
Note: Ensure that no other user is currently using the database file.
-
Start Microsoft Access.
-
On the Template page, double-click on the Blank desktop database.
-
Choose File > Close.
-
Click Database Tools and then select Compact and Repair Database.
-
In the “Database to Compact From” dialog box, search and double-click the database that you want to compact and repair.
4. Compact and Repair Access Database from Command Line
You can also use the Compact and Repair utility via Command Prompt. Follow the below steps:
-
Click the Windows icon and search Command Prompt.
-
Right-click on it and then choose Run as Administrator.
-
Next, type the below Command in the Command Prompt window and press the Enter key:
msaccess
5. Compact and Repair Corrupt Database When Access Prompts You
When you try to open a corrupt database file in MS Access, you may see a prompt to Compact and Repair the database. When you click Yes, the following two things happen:
-
If MS Access has partially completed Access database repair, then it keeps track of the database objects that failed to repair in a system table, named MSysCompactErrors.
-
If MS Access completely repairs the damaged/corrupted database file, it displays the message “the repair was successful” and notifies you to check the database content.
Alternative to Compact and Repair Utility
If the Compact and Repair utility fails to repair the database, you can use an advanced Access database repair tool, such as Stellar Repair for Access. This is a professional Access database repair tool that can repair severely corrupt Access database files (ACCDB and MDB). It also provides preview of the repairable data before saving it. The Access database recovery software helps you to restore the data from the corrupt database without using any backup with complete integrity.
Closure
The Compact and Repair tool is an inbuilt feature in MS Access to repair corrupt databases. This post outlines 5 different ways to use the Compact and Repair option in Microsoft Access. Ensure that you have sufficient permissions and rights before using the Compact and Repair tool. In case the tool fails to repair corrupted MS databases, use an advanced Access database repair software.
No comments yet