Strange Behaviors of an Access Database

• Download source files - 2.9 KB
• Download demo project - 22.81 KB

Introduction

A while ago, I encountered a strange behavior in my application which was using an Access database (.mdb file). I placed a question in the CodeProject C++ forum, and I did not get any answer. I started searching the web, MSDN, etc... and I found out that it's not all my fault. I found the reason in a knowledge base article. Look at its description about the possibility of corruption of an Access database file:

"There are several things that can happen, both inside and outside of Access, that may leave your database file damaged (corrupted). The symptoms of a corrupted database can range from #Deleted appearing in certain records, to you being unable to open one of the objects in the database, to you being unable to open the database file in Access altogether." (MSDN-Knowledgebase:283849)

Since I got no answer for my question in the forum, I thought there are at least some guys out there who might not know this, and also since it's easy to corrupt an MDB file, while it's difficult to understand the reason why the application is not functioning properly, I started writing this article.

The question is what might cause a damaged Access MDB file and also how to repair a damaged access file programmatically.

Background

What Can Cause the MDB File to Corrupt

The knowledge base says:

"There are three main reasons why a .mdb file may become corrupted, as follows:

• Interrupted write operation
• Faulty networking hardware
• Opening and saving the .mdb file in another program"(MSDN-Knowledgebase:283849)

There's a detailed description for each one of these in the knowledge base, but some of the important reasons are as follows:

• Losing power during database write
• Dropping network connection
• Abnormal termination of Microsoft Jet connection, such as having task manager to shutdown application, power loss, manual shutdown. (notice that:Fatal system errors almost always cause abnormal termination, refer to kb to find out more information)
• Forgetting to close ADO or DAO objects that opened (Objects from classes like: Recordset, QueryDef, TableDef, and Database)
• A large number of open and close operations in a loop (more than 40,000 successive open and close operations could cause corruption)
• And worst of all, Opening and saving the .mdb file in another program like Microsoft word. It is not recoverable. All your data will be lost.

"There is no way to recover a .mdb file that was opened and then saved in a different program"
(MSDN-Knowledgebase:283849)

How to Reduce Corruption Possibility

• Avoid all the above
• Compact the database often (the class provided with this article)

Things Can Get Worse

Q291162: AutoNumber field duplicates previous values after you compact and repair a database: Microsoft also announces that after we compact and repair our database, it is possible to encounter Duplicate Autonumber field, and if it's your database key, then...
fortunately this applies just to those who are using Microsoft Jet version 4.0.2927.4 and earlier. "Microsoft has confirmed this to be a problem in Microsoft Jet version 4.0.2927.4 and earlier." (Microsoft KB Q291162). To find out your Jet engine version, try to search your system/32 directory for Msjet40.dll (if you are using v.4 and later) then get the properties of the file. This KB can help you find your version and download the latest version: Q239114.

Another bug exists there and it's "AutoNumber field is not reset after you compact an Access database". I don't describe a solution for this, since there is one already, and it is not really critical. For more information, refer to KnowledgeBase Q287756.

If you lost data and have problems recovering data(yet), this can help you a lot: Q247771 and Q306204.

Using the Code

In order to use the code, follow the steps given below:

1. Copy DBFitter.cpp and DBFitter.h to your project.
2. Check and possibly change the first two #import directives in DBFitter.cpp.
   C++

   #import "D:\PROGRAM FILES\COMMON FILES\System\ado\msado15.dll" 
   	rename( "EOF", "MSADO_EOF" )
   #import "D:\PROGRAM FILES\COMMON FILES\System\ado\MSJRO.DLL" no_namespace

3. Include DBFitter.h, create an object from type DBFitter and use it as follows:
   C++

   CDBFitter fitter;
   if ( !fitter.CompactAndRepair(szDbPath,m_szDBPass) ) {
      AfxMessageBox(fitter.GetLastErrString());
   }
   

   CompactAndRepair has three forms that can be used alternatively. The first one you see above, the other form just gets database file path as input and does not use a password.

   C++

   CompactAndRepair(CString szDbPath)    

   The last one asks for a source, a destination and password, which can be given "" as password to say there is not a password:

   C++

   CompactAndRepair(CString szSrcDbPath, CString szDstDbPath, CString szDbPassword)

4. Don't forget to call AfxOleInit(); in the initialization of your application.

"What to Make Sure of Before You Run the Compact and Repair Utility

Before you run the Compact and Repair utility on a database, make sure of the following:

• Make sure that you have enough free storage space on your hard disk for both the original and the compacted versions of the Access database. This means that you must have enough free storage space for at least twice the size of your Access database on that drive. If you need to free some space, delete any unneeded files from that drive, or, if possible, move the Access database to a drive that has more free space.
• Make sure that you have both Open/Run and Open Exclusive permissions for the Access database. If you own the database, make sure to set these permissions. If you do not own the database, contact its owner to find out if you can get these permissions.
• Make sure that no user has the Access database open.
• Make sure that the Access database is not located on a read-only network share or has its file attribute set to Read-only." (Q283849)

Compact and Repair in C#

Thank God, there is already an article:

• Compact and Repair Access Database using C# and Late Binding