Compact and repairing databases is an essential feature that you can use to reduce database size and corruption. It also improves database performance and consistency. Access may prompt you to compact and repair your database if it detects any corruption with the database.
There are multiple ways to compact and repair a database. Stick with us to learn how to do it.
How to Compact and Repair Database?
Compacting and repairing a database are two different things and processes. Compacting will clear unused spaces, whereas repairing will repair any corruption. However, since they have similar contexts, compacting and repairing are combined into one option.
You may want to back up your database first. Copy your database and paste it into another location. You can also use specialized third-party software to compact and repair databases.
Manually Compact Database
You can manually choose to compact and repair your current database whenever you wish to. Manually compacting can help you compact and repair individual databases. Here’s how you do it:
- Open MS Access.
- Press Ctrl + O and navigate to your database.
- Open your database in access.
- Go to File > Info and Click on Compact & Repair Database.
This will create another compacted file in the same location as the original database. The compacted database will be smaller in size.
Auto Compact on Exit
MS Access also lets you automatically compact and repair a database. This option is disabled by default, and you will have to enable it first. After enabling this setting, MS Access will compact and repair the database when you save and exit the program. Here’s how you turn on auto compacting and repairing.
- Open the database in access.
- Press the File tab.
- Go to options from the left panel.
- Click on Current Database from the left tab.
- Tick the Compact on Close check box.
- Press OK and restart the Access application for the changes to take effect.
Manually Compact Database Without Opening
In case you cannot directly open the database, use this method to compact databases without opening them. Follow these steps to selectively compact databases without having to open them.
- Open MS access.
- Go to File and open a New Blank Database.
- Go to File and select Close.
- Navigate to Database Tools on the top bar and click on Compact and Repair Database.
- Select your database and click Open.
- Choose the location you want to save your new compact database.
From The Command Line
Using the access option to compact can be tedious work. You can use a simple command line to directly compact a database file without opening MS access at all. You can execute the compact process without opening the database itself from the command prompt. Here’s how you do it:
- Press Windows + R to open the Run program.
cmdand press Ctrl + Shift + Enter to launch the command prompt with administrator privileges.
- Click Yes to give administrator privileges.
msaccess <database file location path>\<database file name> /compactand press enter. Replace with database file location path with path of your database and database file name with the name of the database file.
msaccess “D:\Potato folder\database.mdb” / compact
This command will compact and repair the database named “database” located in
D:/Potato folder path.
When to Compact and Repair Database?
Databases grow with time and can become very large in size. When existing data is removed, the space used by that data stays empty. This empty space will not be used and will continue to reserve space as if it holds data. You can periodically compact and repair your database when the size of the database increases.
For example, if you have a 6MB database and you delete half of the data in that database. Technically, the size of the database should be halved. But this doesn’t happen because the database holds that storage space. If you add 2MB more data, the total size of the database becomes 8MB instead of 5MB. Compacting will change the 8MB database into a 5MB file.
Large databases can cause performance issues or even corrupt the data itself. And unless you compact and repair your database, it will keep expanding, ultimately leading to database corruption. A large database is prone to corruption and data fragmentation.
Compacting and repairing your database prevents this by removing such unused space and making it available for usage. It creates another replica of the database after removing all the unused space. It also repairs in case of any corruption in the database. This is why the size of the database decreases after using the compress and repair function.