MS Access database corruption – causes and fixes

Causes:

Hardware failure issue : It is the most common reason for database corruption even, I can say in 80-90% cases. It affects the database integrity which is related to networking and hard-drive.

Software clashes: sometime the 3rd party plug-ins and programs can be the reasons of MS Access database corruption. This kind of applications has their own rights and programming behaviour and they also run in the same memory space as the database.

 Access of multi user: MS access database works on JET database engine, which is a file-based system. When multi-users work on the same time then jet database engine uses lock file (.ldb file) to manage the synchronization between them. By doing this, the speed of the jet database engine becomes slow. The session disconnects due to time-out failure.

Fixes

Always take a backup: The necessary thing which can help you to get your database back is, it’s backup. Always take the backup of your database according to your schedule. If you do not have the database backup and suddenly corruption occurs then, take the backup immediately. This is the first thing which you should do and if you are a DBA then it is your duty.

In case, if you have the previous backup of your database then you can get back your database structure because corruption is the part of Access database structure rather than jet database format.

Delete the .LDB file: This file is used to synchronise multi-user file operation therefore, it doesn’t need to repair MS Access database. If a user won’t delete this file, then it is high chances that user or program will be logged into the database. With this, you should close all the open instances of MS Access database.

Compact & Repair: This command prevents your database by following problems and file growing larger is one of them. Access database file becomes larger after every operation and this command makes the file smaller by removing the unused space from it.

Note: It doesn’t compress the database.

If the database file is in the shared network and multi-users directly working on it then there is a small risk of the corruption. It will become riskier when users edit the data in the memo field.

Microsoft JET Compact utility: Jet.Comp.exe is a utility which is developed by Microsoft to correct minor corruption of the database. It is the most successful built-in feature of MS Access to repair Database corruption. You can read more about JET compact utility here: http://support.microsoft.com/kb/273956

Decompile the MS Access VBA Code: To decompile the VBA code, run the following command from the command line.
C:\Program Files\Microsoft Office\Office\MsAccess.exe /decompile C:\DatabasePath\FileName.mdb
Note: it is an example, please change them path according to your system.

 

Third Party Tool: Stellar Repair for Access is able to repair corrupt .mdb and .accdb files. It is 100% secure software for MS Access database repair. It works on MS Access 2016,  2013, 2010, 2007 and other older versions

Conclusion: These are the few points which you should remember. The database corruption can occur by a small mistake so, always take care of it.