IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
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.
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
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.
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.
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.
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 losing table records randomly
- #DELETED# appears in tables when multi-user access is not used
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;
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.
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.
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.
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.
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.
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).
View more information about JetComp on Microsoft's Support web site: http://support.microsoft.com/kb/273956
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.
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.
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:
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!
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
Corruption Prevention Articles
Common Corruption Symptoms
ACC2000: Database Password Appears Even Though It Was Never Set.
ACC2000: How to recover data from a damaged database table or a corrupted database table in Access 2000, Access 2002 or Access 2003
ACC2000: "Unexpected Error 35012" When You Open a Database.
ACC2000: Error Message: "Visual Basic for Applications Project in This Database Is Corrupt".
ACC2000: Binary Field Appears Corrupted in Database from earlier version.
ACC2000: How to Troubleshoot Invalid Page Faults in Microsoft Access 2000.
ACC2000: How to Use MS Query to Recover data from a Damaged Jet 4.0 Database.
ACC97: You cannot open or convert an Access 97 database.
ACC2000: Unexpected Project Corruption in Access Database.
ACC2000: Error Converting a Database with a Large Number of Forms That Have Modules.
ACC2000: Cannot Open Visual Basic Environment from Design view of a database object.
ACC2000: Jet Compact utility available in download center.
ACC2000: Table Corruption after referential integrity checks.
ACC2000: How to Troubleshoot Invalid Page Faults in Access 2000.
ACC2000: Unexpected characters in Database converted from localised version of Access
ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database.
ACC2002: How to Troubleshoot and Repair a Damaged Access 2002 Database.
ACC2002: How to Troubleshoot Fatal System Errors in Access 2002 Running on Microsoft Windows 2000.
ACC2002: How to Troubleshoot Fatal System Errors in Access 2002 Running on Windows Millennium.
OFFXP: How to Read Error Signature from Office Application Error Reporting If Program Quits Unexpectedly.
What Are Fatal Exception Errors.
Troubleshooting 'Stop 0x00000077' or 'KERNEL_STACK_INPAGE_ERROR'.
How to Troubleshoot 'STOP 0x3F' and 'STOP 0xD8' Error Messages.
General Causes of STOP 0x0000007F Errors.
Troubleshooting 'Stop 0x0A' Messages in Windows NT
Corrupted VBA Project when you try to convert a database.
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.
Have your say - comment on this article.
What did you think of 'Access Database Corruption Repair Guide'?
|Chris Mundahl says...|
04 June 2008
This article is great. I felt like I was on an Island by myself with thes Access problems (35 users here to keep up with). Looking forward to trying some of your suggestions, including moving to SQL server ASAP. Also will visit with you about analyzing my migration strategy to a more non destructive or stable platform, Thanks a Million, Chris M.
|zubair irshad says...|
17 September 2009
This is one of best error pages for access corruption i have ever come across. it will definitely be adding a lot to my knowledge of access (although i have roughly about 3 years of experience with access 97 - 2003). Praying that this guy goes to heaven.
01 March 2010
Wow, Wayne! Like everything else you do, this is ABCD (Above and Beyond the Call of Duty). This wonderful work belongs in an Access wiki ... ;)
(: have an awesome day :)
|Perry Z. says...|
12 March 2010
Just wanted to say that this answers a lot of questions about the problems my clients are having. I will start looking to improve my coding, and training users to keep their hands away from the power switch. Interestingly, today the "fault" fixed itself, but now I understand why that may be so.
20 September 2010
Your decompile-tip saved my day - thanks a bundle!! Symptoms were: MS Access XP hangs when opening a form with code, hangs when trying to import forms and modules in new empty database, just thought i lost a days work..
P.S.: I use Access just as front end for PgSQL - i'm definitely not masochist enough to trust it my data!
Have your say...