Image : http://www.flickr.com
If you find that your Access database has become very large in size, you might believe that removing unwanted records would clear some space for you. However, this does not always work since frequent creation and deletion of records leads to fragmentation of the database. The Compact utility of Microsoft Access is the best tool for actually getting rid of empty space in an existing database. However, it also has certain limitations, as we will see in the following article.
The Compact and Repair utility provided by Microsoft Access is a wonderful tool that helps in quickly resolving any minor damage caused to an MDB file. If you suspect that a database you are working with is suffering from corruption or if the file is completely inaccessible, you should run the Compact and Repair utility immediately. The Repair utility is very effective when it comes to repairing the tables, queries and indexes in the corrupt database. However, this utility does not attempt to repair interface objects such as forms or reports. In fact, it might not even be able to recover all the data from a severely damaged database - for a full Access recovery, you might have to employ a more powerful tool such as Advanced Access Repair.
On the other hand, the Compact utility primarily gets rid of the unnecessary empty space that is created in an existing database. When records are created and deleted on a regular basis, there is de-fragmentation that results in a lot of empty space lying around unused. The Compact utility creates a new destination database and then carefully copies each object from the old database into the new one, ensuring that all the space is properly made use and that there is no de-fragmentation. This improves the performance of the database and also reduces the risk of any damage to the file due to large de-fragmentation that might require you to take steps for Access recovery.
The Compact utility also performs the following actions -
While creating the new database, it reorganizes the pages of the table so that they now reside in database pages that are next to each other. Since the tables are no longer fragmented, this dramatically improves the performance of the Access database.
As mentioned earlier, when records or even interface objects are created and deleted, the empty space that is created is simply marked as being available for new records or objects. The space is never actually freed up and as such the size of the database never shrinks due to deletion of records or objects. To truly free up the space, you need to carry out the Compaction process. In fact, the more often you add, delete or update records and objects, the more often you have to carry out the Compaction process.
There are however, some limitations to the Compact and Repair utility. Before you run this tool, you have to make sure that there is enough space on the disk for both the original database and the compacted version. Users have to be prevented from accessing and modifying the database when this utility is being run. Further, the Compact and Repair utility is not able to repair severe damage caused to the Access database that may occur typically due to hardware failures.
A full Access recovery can be carried out by using Advanced Access Repair. This tool can recover almost all the data from the damaged database, across many versions of Access, including deleted tables and records.
Information about the product can be collected at http://www.repair-access.com/.
