A database can grow larger, especially when you add more data to your databases. Access databases can occasionally become corrupt or damaged or deleting objects such as records, tables, forms, or queries. The space that was formerly occupied will not be available for additional objects. Instead, additional objects are given new space that was not already assigned. To remove the empty space from the database, you can compact the database or repair problems that might occur in the database. Using Compact and Repair will make the database smaller by eliminating unused space; it can also help to improve the performance of the database.
How to compact and repair Access database
Follow the steps in the compact and repair in Access:
1] How to compact and repair a database in Access manually
- Open the database to be compacted.
- Click the File tab.
- On the backstage view, click the Info tab.
- Click the Compact and Repair Database button.
- Access creates a copy of the compacted and repaired database in the same location.
2] Automatically repair the database in Access as it closes
In Access, you can automatically compact and repair a database when it closes; follow the steps below to do this action-
- Click the File tab.
- On the backstage view, click the Options.
- An Access Options dialog box will open.
- Select Current Database on the left pane.
- Under the section Application Options, check the checkbox for Compact and Close. Selecting this option affects the database that is currently open. Set this option separately for each database that you want to compact and repair.
- Then click OK.
- Close the database so that the option can take effect.
3] Manually compact and repair an Access database that is not open
Select File and click Close on the left pane on the backstage view.
Click Database Tools and click Compact and Repair Database in the Tool group.
A Database to Compact From dialog box will open.
In the dialog box, choose an Access File and click the Compact button.
Access creates a copy of the compacted and repaired database in the same location.
Read: How to create a Database in Microsoft Access
What is the purpose of compacting data?
Compact and Repair are used for two reasons to repair corrupted data and to eliminate wasted space. A database file can be corrupted if shared over a network or multiple users are using it simultaneously, which can corrupt the file. The Compact and Repair feature is needed to fix this issue. Whenever individuals add, update a database, and change its design, the database will grow larger. Due to the growth in your database, objects will open slowly, queries may take longer to run, and normal operations will take longer to perform, so Compact and Repair are needed to remove space from the database.
Read: How to set a Password on an Access Database
What is Compact on close in Access?
Compact and Repair is a feature in Access settings that compacts and repairs Access databases. The compact on close option in the Access settings is checked when you want to automatically repair a database as it closes. In this tutorial, we will explain the process of how to automatically repair a database as it closes.
We hope this tutorial helps you understand how to use the compact and repair in Access; if you have questions about the tutorial, let us know in the comments.