Access Database Corruption Repair Guide

1.	What is Database Corruption?
2.	What causes Database Corruption?
3.	Identifying Database Corruption
4.	How to fix Database Corruption
5.	Microsoft KB Articles relating to Database Corruption
6.	Tips to prevent future Database Corruption

What is Database Corruption?

Database Corruption is when an error occurs in the binary file format of a particular database.  The result can be an unusable database or one where inconsistent behaviour occurs (such as random errors).

Access MDB Database corruption is actually very common, however Access (and the Jet Database Engine) will fix minor corruption issues in databases during the normal use of reading/writing table data.  Therefore in many cases your database will become corrupt but you will never know anything about it!  This can be very risky because corruption often spreads and gets worse over time if not fixed.

What causes Database Corruption?

There are a great number of issues that can lead to database corruption.  The most common are;

1. Hardware failures

The most common hardware issues that can affect database integrity relate to Networking equipment and Hard drives.  Network cards that lose packets and hard drives that have bad sectors are highly likely to cause database corruption (as well as many other computer failures!).

You should have all networking equipment and hard drives tested regularly to prevent such problems.

As I'm sure you're aware, any hardware failure in your machine can cause applications to crash etc.  Unfortunately Jet databases are particularly sensitive to application crashes.  This is because Jet works at a file-system based level and thus if your application crashes, Jet also ends any writing in progress immediately without finishing (unlike a client-server based model such as SQL Server). 

2. Software conflicts

Having 3rd party plug-ins for Access installed on your machine and using them in your databases can lead to corruption if they have not been designed correctly.  This includes 3rd party ActiveX controls.

Remember that these plug-ins are programs in there own right and since they often run in the same application memory space as Microsoft Access if the plug-in crashes, so does Access.

3. JET Software conflicts

The Jet Database Engine has changed considerably over the years.  Although the engine is now at a fairly mature level, it has taken many revisions and updates to get to the current version.  Since its release, the Jet engine has had 4 major release versions, however each of these have had numerous service packs and updates.

Very often database corruption can occur when you have one database shared between multiple users that have different Jet service packs and updates installed. This is due to minor differences in the mdb file format and file locking mechanisms.  Luckily this situation is much improved in the latest version of Jet, v4.

4. Multi-user access

Since the Jet database engine is file-based (unlike SQL Server which is client-server based), in order to enable multiple users over a network to share the same file, the Jet engine uses a Lock file (.ldb) in order to synchronise database read/writes.  Unfortunately using this Lock file relies on Windows OS API calls to handle the file synchronisation which in turn relies on file-transfer networking protocols.  All network connections have a bandwidth limit, and any other software on your machine transferring data over the same network socket could potentially slow down the Jet communication, when this happens the connection often disconnects due to a 'time-out' failure.  When this happens, the database is left in a 'suspect' state and often needs repairing before allowing to continue.

Most of the time network problems leave the database in a 'suspect' state but don't cause corruption - it depends what the user was doing when then problem occurred.  For example, if the user was doing a large SQL Append query, it is likely the database will be corrupt. 

If the file is stored on an Windows NT based file server, consider disabling 'opportunistic locking' (oplocks) on the file server.  Opportunistic locking improves file i/o performance but increases the risk of database corruption in a multi-user environment.

5. Access and Jet Bugs

Although Microsoft will probably never admit that Access or Jet are actually responsible for some forms of corruption due to bugs in their software - evidence over time suggests that this is this case.  Most notably, the 'Name Auto-Correct' feature of Access is the most well known bug that causes all sorts of problems and does increase the chances of corruption.  You should always disable this feature when starting a new database - in my opinion the benefits do not outweigh the consequences.

6. Inefficient Database Design

Due to the technicalities of having a database format designed for multi-user access at file level, Jet/Access Databases are renown for causing severe bloating to the file size.  Unfortunately in a network environment with multi-user access to a Jet Database this extra bloating puts extreme pressure on any network protocol and thus increases the risk of corruption detrimentally.  Generally speaking, multi-user databases with a split front end GUI and back end database offer much improved network performance and less corruption risk than the one-database-for-all approach. 

Very often Access and the Jet Database Engine deals with minor corruption issues without informing the user.  This means that if there is an intermittent problem with your hard drive or network equipment then you will only get to know about the problem after it has caused reasonable damage to the database format.  Some would say this is a good idea, but personally I'd prefer to know about the problem and deal with it before it gets worse.  

The importance of a Backup Plan As Access Database Repair specialists, our users often ask how they can prevent corruption.  There is a lot that can be done to try to prevent corruption, however it is impossible to be 100% safe - this is why we always recommend having a regular backup strategy in place.  Some types of corruption can be fixed easily - others can't.  With a recent backup available, even if the worst happens, all is not necessarily lost.  At EverythingAccess.com we recommend "The Access Workbench" utility by GR-FX for making backups of your Access Databases safely when the database is not in use - download the demo and give it a try.  Obviously this utility won't save you if you've already suffered corruption - but it is a very good investment for future.  Read on to find out your options if you have already suffered corruption in your database.

Identifying Database Corruption

Due to the extensibility and flexibility of the Access database format, it is sometimes difficult to determine whether an Access database is corrupt.  This is because errors and crashes can be caused from plug-ins, VBA code, macros, or even hardware.  There is no definitive way to find out if an error occurring in Access is due to corruption other than by investigating the binary structure of the file, however by following these steps you will know with a high probability whether the problems you are having are due to corruption or other means.

1.	Ensure you are testing the database on your local machine If working on a local network make sure you copy the file from the network onto your local machine for testing.  Since the Jet database engine works at file-level many problems in Access can be attributed to network specific communication problems.  If the problems disappear once running the database on your local machine, consider upgrading your network equipment - primarily network cards are a common source of this sort of problem.
2.	Test the database on another machine with Access installed Try to test the database on a clean installation of Access if possible.  If the errors/crashes do not occur on another machine, the errors are unlikely to be due to corruption - try removing any unnecessary hardware from your machine and uninstalling any Access plug-ins.  If the problems still exist, the problems could be being caused by another software product conflicting on your machine - surprisingly, re-installing Access will fix most of these conflicts. If the problems still exist on the second machine continue to Step 3.
3.	Check your error message/problem against this list of common Corruption error messages/symptoms Unrecognized Database Format. (Ensure you are using the correct version of Access) This database is in an unrecognized format. The database may have been created with a later version of Microsoft Access than the one you are using. Upgrade your version of Microsoft Access to the current one, then open this database. Enter password prompt (when one has never been applied! - try Access Password Retrieval before assuming corruption) The Microsoft Jet Database Engine cannot open the file The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time (when only one user is logged into the database) AOIndex is not an index in this table This database is in an unexpected state; Microsoft Access can't open it..  The database has been converted from a prior version of Microsoft Access by using the DOA compact database method instead of the convert database command on the tools menu. This has left the database in an unconverted state. (Often a sign that Access crashed or stopped unexpectedly during a Compact/Repair) This database is in an unexpected state; Microsoft Access can't open it. This database has been converted from a prior version of Microsoft Access by using the DAO CompactDatabase method instead of the Convert Database command on the Tools menu (Database Utilities submenu).  This has left the database in a partially converted state. If you have a copy of the database in its original format, use the Convert Database command on the Tools menu (Database Utilities submenu) to convert it.  IF the original database is no longer available, create a new database and import your tables and queries to preserve your data.  Your other database objects can't be recovered. (Often a sign that Access crashed or stopped unexpectedly during a Compact/Repair) You do not have the necessary permissions to open this object. Please contact your system administrator. (when user level security has not been set) *** isn't an Index in this table. Look in the index collection of the TableDef object to determine the valid index names  Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience. Unexpected error 35012 (opening file) (possibly network related corruption) This object needs a newer version of the Microsoft Jet database module (Check you have the latest service packs installed before assuming corruption) Operation failed - too many indexes - reduce the number and try again Access crashes (Invalid Page Fault/GPF) when opening the database The instruction at "0x????????" referenced memory at "0x????????". The memory could not be 'written' (remove any Access plug-ins before assuming corruption) The Visual Basic for Applications Project in this database Is Corrupt The Microsoft Jet Database Engine could not find object Msys*/databases.  Make sure the object exists and that you spell its name correctly and the path name correctly Could not find field '***' Invalid Bookmark Access starts loosing table records randomly #DELETED# appears in tables when multi-user access is not used
4.	What if my error isn't in the list above? The list above is a general guide only.  Your problem could still be due to Access corruption, particularly if the errors occur randomly or during a compact/repair.  If you are still unsure, do a web search for your specific error message/code. If you can't find any information on the web, there's no harm in following this guide in trying to fix Access corruption even if you are unsure whether the database is corrupt or not. If you need help or advice, feel free to e-mail our repair team to discuss your problem further; support@everythingaccess.com

How to fix Database Corruption

Before sending your corrupt database to an Access Database Repair company you should first try following this simple guide to try to fix the corruption yourself.  Often the corruption level if treated immediately and correctly is only minor.  If the corruption is left untreated then it is likely to worsen considerably in a short period of time. If after following this guide the corruption is still present, the easiest solution is to send your file to an Access Database Repair company such as EverythingAccess.com for a free Recovery Report listing all recoverable tables, queries, forms etc.

1.	Backup the corrupt file as soon as possible The repair utilities used in this article can sometimes have an adverse effect on treating the corruption if the corruption level is high.  Therefore you should always ensure you have a backup of the corrupt file before trying to repair it.
2.	Delete .LDB file When an Access database is opened by Access or the Jet database engine a new file with extension .ldb is created.  This file is used for synchronizing multi-user file operations and is therefore not needed for repair purposes.  If you cannot delete the .ldb file then it is highly likely that a user and/or program is still logged into the database.  You should close all open instances of MsAccess.exe (using the Windows task manager if necessary) and also close any software that could be logged into the database. If you still cannot delete the .ldb file, try restarting the computer that the database is stored on.
3.	Consider restoring the database from a previous backup You should keep regular backups of your database data and designs in order to try to limit the potential problems caused by database corruption (or any other computer based failure!). Often the corruption is actually part of the Access objects structure and not part of the underlying Jet database format.  Therefore if your database contains Access specific objects, like forms or reports, you might want to try deleting the tables in the backup file and 'importing' just the tables from the corrupt database into the backup. You can import the tables using different methods, such as; Delete the tables from the backup file, then use the Access 'Import Wizard' to import all table structures and data into the backup database. Delete only the table data from the backup (open the table and delete all rows).  Now create Jet link tables to the corrupt database tables.  For each table create an Append SQL Query to copy the data from the link table into the backup table. Delete only the table data from the backup (open the table and delete all rows).  Now use VBA code to copy the data (by using DAO) from the corrupt database over to the backup file. Delete only the table data from the backup (open the table and delete all rows).  Now use VBA code to copy the data (by using ADO) from the corrupt database over to the backup file. From our experience, options 3/4 are usually the most effective as you can process each row in the table individually and you can programmatically 'ignore' certain rows if they are deemed to be corrupt.
4.	Use the built-in Access 'Compact & Repair' feature Microsoft Access has a very useful feature called 'Compact and Repair' accessible from the Tools/Database Utilities menu.  Note: You can access this menu option even when no database is loaded into Access, therefore if your database won't even open you can still try this method. Be warned: Ensure you have made a backup as this method can sometimes make the corruption worse.
5.	Use the Microsoft Jet Compact Utility Microsoft have developed a utility called JetComp.exe which has been designed to try to correct minor corruption issues in Access databases.  In our experience this is much more successful in repairing corruption than the built-in feature of Access (#3 from above).   Download JetComp.exe now View more information about JetComp on Microsoft's Support web site: http://support.microsoft.com/kb/273956
6.	Decompile Microsoft Access VBA code When VBA modules get compiled in Access, the compiled code is very much dependant on the VBA p-code parser library.  Unfortunately there are a few variants of this library which can lead to corruption if the compiled code is run on several machines with different versions of this library installed.  Fortunately Microsoft added a command line switch to Access which allows users to remove all compiled VBA codes from an access database, leaving only the non-compiled source-code in place.   You should try the command line switch if you can gain access into your database but strange errors occur when running any VBA module/class code.  To run the 'decompile' option from Access you must open Access with a command line option of '/decompile', for example, run the following from a commandline: C:\Program Files\Microsoft Office\Office\MsAccess.exe /decompile C:\DatabasePath\FileName.mdb Please note that you may need to change the path shown above for MsAccess.exe if you installed it to a different location.  After the decompile has finished, you should close Access and then re-open it and compile all modules again.
7.	Import tables into a new database Create a new database then try import the tables using different methods, such as; Use the Access 'Import Wizard' to import all table structures and data into the new database. Recreate the table structures in the new database manually. Now create Jet link tables to the corrupt database tables.  For each table create an Append SQL Query to copy the data from the link table into the new table. Recreate the table structures in the new database manually.  Now use VBA code to copy the data (by using DAO) from the corrupt database over to the backup file. Recreate the table structures in the new database manually.  Now use VBA code to copy the data (by using ADO) from the corrupt database over to the backup file. From our experience, options 3/4 are usually the most effective as you can process each row in the table individually and you can programmatically 'ignore' certain rows if they are deemed to be corrupt.
8.	Send your file to an Access Repair Company If all else fails, we recommend you send your file to an Access database repair company such as ourselves: EverythingAccess.com We have custom designed software to recover all database objects such as tables, link-tables, queries, forms, reports, macros & modules.  We have extensive knowledge of the raw Jet database format and have a 99% success rate in recovering all database objects.  We provide you with a free no-obligation Recovery Report within minutes of you uploading the file to us. Don't despair - we repair! Try our repair service today!

Microsoft Knowledge Base Articles on Access Corruption

We have compiled a selection of links to Microsoft Knowledge Base articles that relate to errors and problems that occur in Microsoft Access which could be due to corruption of the database involved.  We have also included some articles that are intended to help users and developers alike in solving common mistakes that will eventually lead to database corruption.

If you are experiencing any of the issues suggested below and have tried the standard repair procedures outlined by Microsoft but to no avail, please feel free to upload the file to us to receive a no obligation quote and Recovery Report for us to fix the file for you. 

Troubleshooting/Repairing Access Databases

Q209137	ACC2000: How to Troubleshoot/Repair a Damaged Jet 4.0 Database.
Q279334	ACC97 How to Troubleshoot/Repair a Damaged Jet 3.5 Database.
Q109953	ACC95: How to Troubleshoot/Repair a Damaged Jet 3.0 and Prior Databases.

Corruption Prevention Articles

Q300216	ACC2000: How to keep a Jet 4.0 database in top working condition in Access 2000.
Q198755	ACC2000: How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2000 (including determining 'Suspect' users)

Common Corruption Symptoms

Q243895	ACC2000: Database Password Appears Even Though It Was Never Set.
Q247771	ACC2000: How to recover data from a damaged database table or a corrupted database table in Access 2000, Access 2002 or Access 2003
Q277924	ACC2000: "Unexpected Error 35012" When You Open a Database.
Q248960	ACC2000: Error Message: "Visual Basic for Applications Project in This Database Is Corrupt".
Q225925	ACC2000: Binary Field Appears Corrupted in Database from earlier version.
Q208488	ACC2000: How to Troubleshoot Invalid Page Faults in Microsoft Access 2000.
Q304561	ACC2000: How to Use MS Query to Recover data from a Damaged Jet 4.0 Database.
Q248894	ACC97: You cannot open or convert an Access 97 database.
Q280433	ACC2000: Unexpected Project Corruption in Access Database.
Q225949	ACC2000: Error Converting a Database with a Large Number of Forms That Have Modules.
Q296857	ACC2000: Cannot Open Visual Basic Environment from Design view of a database object.
Q273956	ACC2000: Jet Compact utility available in download center.
Q296389	ACC2000: Table Corruption after referential integrity checks.
Q208488	ACC2000: How to Troubleshoot Invalid Page Faults in Access 2000.
Q222446	ACC2000: Unexpected characters in Database converted from localised version of Access
Q257408	ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database.
Q283849	ACC2002: How to Troubleshoot and Repair a Damaged Access 2002 Database.
Q294301	ACC2002: How to Troubleshoot Fatal System Errors in Access 2002 Running on Microsoft Windows 2000.
Q284152	ACC2002: How to Troubleshoot Fatal System Errors in Access 2002 Running on Windows Millennium.
Q289508	OFFXP: How to Read Error Signature from Office Application Error Reporting If Program Quits Unexpectedly.
Q150314	What Are Fatal Exception Errors.
Q228753	Troubleshooting 'Stop 0x00000077' or 'KERNEL_STACK_INPAGE_ERROR'.
Q256004	How to Troubleshoot 'STOP 0x3F' and 'STOP 0xD8' Error Messages.
Q137539	General Causes of STOP 0x0000007F Errors.
Q165863	Troubleshooting 'Stop 0x0A' Messages in Windows NT
Q225946	Corrupted VBA Project when you try to convert a database.

Tips to prevent future Database Corruption

1. (Access 2000+) Disable 'Name Auto-Correct' feature by going to the following menu: Tools / Options then selecting the General tab. Read why here.
2. Don't run/open your database over inherently volatile networks such as Wi-fi/Bluetooth.
3. Restrict multi-user access as much as possible.  Consider upgrading to SQL Server/MSDE (or similar) when your database is being used by 10 or more users.
4. If running in a multi-user environment, consider splitting the front end GUI from the back end database to minimize network usage.
5. For each machine connecting to the database, run ScanDisk/CheckDisk regularly.  Replace bad drives, don't just mark bad sectors as inactive.
6. Don't install unnecessary software/plug-ins. 
7. Ensure all computers have the latest service packs for your version of Windows, MDAC, Access and Jet.
8. Ensure you have plenty of memory (physical and virtual) available on your PC. 
9. Try to run the same version of Access on all machines (for example, sharing an Access 2000 database between Access 2000 users and Access 2003 users is best avoided)
10. Avoid mixing operating systems in a multi-user environment. 
11. Avoid storing the database on a non-NTFS formatted hard drive where possible.
12. Regularly carry out stress tests on network equipment.
13. Disable opportunistic locking (oplocks) on the file server.  This is only recommended if Access databases are the primary storage on the server as this will have an adverse affect on normal network performance on the server.  Consult your network administrator to do this.
14. When using ADO from pure VB code, use the Microsoft Jet OLEDB driver instead of the Microsoft Access ODBC driver.
15. Run the 'Compact & Repair' feature within Access on a regular basis to improve efficiency and lower the risk of corruption.
16. When using Access objects (e.g. forms, reports, modules etc.) consider upgrading Access 2000 databases to Access 2002/2003.  This is due to a much improved file-system for storing these objects in Access 2002/2003.
17. Consider 'decompiling' the database after making substantial changes to VBA code to avoid bloating. (see 'Decompile Microsoft Access VBA code' above in this article)
18. When developing, and before deploying your database, create a new database and 'Import' all the objects from your existing database.  This will remove all redundant data, increases performance and reduces the risk of corruption.
19. Do not use the IPX protocol on Windows NT Server where Jet databases are located across the network and the client is Microsoft Windows 9x with Internet Packet Exchange/Sequenced Packet Exchange (IPX/SPX). Instead, run TCP/IP on the Windows NT Server and a dual protocol stack of IPX and TCP/IP on the Windows 9x client. (Windows NT to Windows NT with IPX/SPX will not cause a problem, nor will Novell to any client).
20. Train users to properly shutdown your Access application and to always wait for it to close on its own rather than Ctrl-Alt-Del to terminate the process (even if it seems like it's not responding - be patient!)
21. Do not use the Access 'Encode' menu option! 'Encoding' encrypts the database but increases the likelihood of corruption occurring and also reduces the chances of repair if corruption does occur by ten fold!!  Plus, the 'encoding' is completely reversible since the encryption key is stored in the file header - so it is simply pointless anyway!    
22. In VBA code always specifically close objects like recordsets - don't just de-reference them by setting them to Nothing.

Last but not least (not really a prevention method, but still worth repeating), backup your databases regularly!

Feel free to contact us if you require any further help with your corrupt Access databases. ContactUs.htm

*Microsoft Access is a trademark of Microsoft Corporation in the United States and other countries*

IMPORTANT: This document may not be reproduced in part or whole without prior consent from the author.

http://www.everythingaccess.com