In this article, we have addressed three different ways using which users can compact their MS Access Database, and what are their possible pros and challenges.
In this article, we will discuss three different ways of compacting MS Access Database.
- Compact on Close
- Compacting Current Database in UI
- Compacting another Database in UI
1. Compact on Close
Steps: Open File > Select Options > Open Current Database > Select Compact On Close (checkbox)
This option is particularly used by experienced Access developers. Regular compacting can significantly help in maintaining the performance of your system; however, it’s rare that you have to compact a database whenever it closes. Compact on Close is potentially more prone to corrupt your data. So, anytime you compact your files, it includes a minute risk of corrupting it, as every time a user closes his/her database, it also increases the frequency of changes. This is one main reason why most Access users like to steer clear off of this method.
2. Compacting Current Database in UI
Steps: Select Database Tools tab > Open and run Compact and Repair Database
MS Access allows users to compact current database through the Menu/Ribbon, provided he/she is the only one who has that particular database open in their system. Compacting process writes all the record in a new database while deleting the old one and also renaming the new database with the old name. I assume that you can already see why this can cause a risk of corruption. If due to any reason the system stops during the writing process, the new database will be corrupted. And now that the old database is deleted, the user will be left with nothing to work on.
Usually, even if the user is able to extract the file, it is corrupted beyond repair. However later MS Access editions have considered this problem and are being designed with an anti-corruption interface. However, we would still suggest our readers to create a copy of their database, before even planning to compact it.
Even a minor glitch in the network is enough to cause data damage. So, copy the database to a local drive, then compact it and copy it back into the system. By doing this user is able to ensure that the original copy of a database is preserved at all costs.
3. Compacting another Database in UI
Users can compact closed database files, which are not open at the moment.
Steps: Open Access > Select Database Tools tab > Open Compact and Repair Database
Users can compact Database for a closed database using the same method as for compacting current database. But while compacting another database, Access will display two dialog box, ‘Database to Compact From’ asking the user to name the file which is being compacted and other ‘Compact Database Info’ in which the user need to give a new name to the compacted database.
Note: Users can’t assign the same name of the original file to the new database file, so it helps in safeguarding the original database of the users. It also means that the user will have to rename the new compact database file manually in the system. This is considered as the safest method for compacting the database as it ensures to keep your original database separate from the compact version.
At times, all steps to compact a database may fail. In such a scenario, you need to immediately get hold of a specialized Access fix tool.
Author Introduction:
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com
1