One of the most powerful features of Microsoft Access database is that it allows databases to consistently increase in size. This way you do not have to worry about size limitations while developing the databases or while working on them. On the other hand, this means that even if you delete a record or even entire tables, Access simply indicates that the space may be made use of, for new records without actually giving up the space. Similarly, even if any extra space is created when you shorten or possibly modify records, that space is not released. This will not only cause enormous amount of defragmentation of the database, but more importantly it will eventually lead to corruption in the MDB files. The only way to restore data from the damaged files may then be to resort to high level MDB recovery.
The best way to make use of the extra space and the space created by deletions is to regularly compact the Access database. On the other hand, not compacting the Access database at all will mean that the extra space is being wasted and the database will continue to demand more space as more records are added.
Fortunately, Microsoft Access provides a powerful Compact and Repair tool that can be used to make use of the space available and to prevent excess defragmentation of the database. This tool works in two phases – in the first, it compacts the database so as to get rid of all the extra space and in the second, it repairs the MDB file if the fragmentation had caused any damage to it. The tool also helps in reducing the physical size of the database thereby making it more manageable for Access and also reducing the risk of requiring an extensive MDB recovery process. In fact, compacting an Access database is the only way that you can actually reduce the size of the database.
The frequency with which you should run the Compact and Repair tool depends a great deal on how often you use your Access database and more importantly on how frequently you add, modify or delete records from the database. If you are a regular user, then it is important that you run the tool often to reduce the risk of corruption in the MDB files. If you do not compact your database regularly, chances are that the MDB file may become corrupted at some point in time. You can then use the Repair utility to actually repair the corrupted MDB file. However, you should bear in mind that this Repair utility can only restore corrupted data objects such as records, tables and indexes.
If after running both the Compact and Repair tools, your database still appears damaged, there is no need to panic. It should still be possible for you to restore almost all your data but you will need to use more specialized, third-party tools such as Advanced Access Repair from DataNumen to carry out an MDB recovery. While these tools are very powerful and have the ability to restore data from seriously corrupt MDB files, they are also very efficient and easy to use.
The official website of Advanced Access Repair is http://www.repair-access.com/.
Article Source: http://ezinearticles.com/expert/Alan_Nice/290089
This article explains why and what the ‘Compact and Repair’ feature is when using Microsoft Access databases (version 2003 or earlier) though it also applies to later versions (Access 2007 and 2010 uing the newer file extension .ACCDB).
The author is spot on regarding why you use it and how it basically works which is very clear. I like to refer to ‘Compacting’ the Access database which reduces the file size issue (as a fragmented file) by ‘squeezing the air out in between‘ bringing the file into a normal manageable size.
The added point to mention here is the ‘Repair’ element which also releases the locking file (.LDB file) when used in a multi-user environment and therefore adminstrators should ensure they are the only user using the database (as exclusive mode) before running this utility.
Tags: accdb files, access 2003, Access 2007, Access Database, compact and repair in access, corrupted files in access, mdb files, repairing access database files, why compact and repair in access
[…] This post was mentioned on Twitter by Ben Beitler, Ben Beitler. Ben Beitler said: #uibc completed my task today – posted an article http://benb.uibcsites.com/?p=44 […]
This statement:
“Similarly, even if any extra space is created when you shorten or possibly modify records, that space is not released. This will not only cause enormous amount of defragmentation of the database, but more importantly it will eventually lead to corruption in the MDB files.”
…is patently false. Slack space never corrupts a database. The article should be edited to remove this disinformation.
Thanks for your comment.
I will agree that modifying a record generally will not cause corruption as you correctly point out “Slack space never corrupts a database.”
But it does change the file size when any record is edited (shortened or lengthened) and therefore in the context of why users should compact a database, it keeps to the decision making of running this utility as an essential administrator’s task.
Without confusing the audience of how fragmentation works, it’s an accurate statement to encapsulate this comment to cover any unusual ‘potential’ corruption that modifying data could directly or indirectly have (more so for related data indexing).
This article was written by the author in good faith and if you feel this is still not a satisfactory answer, then I would be interested to know how you and the author come to an agreement on the matter. I will happily state my case if asked to.
Thanks again.
[…] Why it is Essential to Regularly Compact and Repair Microsoft Access Databases? | Access Database Tu… […]
Cheap Oakley Sunglasses…If some one desires expert view regarding blogging and site-building then i recommend him/her to pay a quick visit this blog, Keep up the good job….