Database corruption (1 Viewer)

[WindSailor]

Viewing an email from Code Project, I ran across this article about database corruption and thought it was worth posting here...
Be sure and click on the immediate link below to view the latest updates in the discussion area at the bottom of the web page.
----------------------
http://www.codeproject.com/useritems/rdb.asp

Strange behaviors of an access database
By Hamed Mosavi.

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 it's 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 don't know this , and also since it's easy to corrupt a 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 an .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 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 MS word. It is not recoverable. all your data will be lost.

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

How to reduce corruption possibility

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

Things can get worst
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." (MS 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 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 problem recovering data(yet), this can help you a lot: Q247771 and Q306204.

Using the code

In order to use the code, follow the following steps:

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

#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:

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

CompactAndRepair has 3 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 password

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

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#
Thanks god, there is already an article: http://www.codeproject.com/cs/database/mdbcompact_latebind.asp

--------------------------

If there are any questions or feedback; please direct them to the original authors link at the top of the page so everyone can benefit.

Thanks.

 

[boblarson]

Some great information - thanks for posting.