If Microsoft Jet is writing data at the time when a problem occurs in a database, such as after a power outage or an inadvertent reboot, then the database may have corrupted data.
Jet sets a flag when it's writing to a database. It resets the flag when the write is complete. If the write operation is interrupted, the flag will remain set. Then when you attempt to open the database again, Jet will see that the flag is set and will give a corrupt-database message. Usually the database is not corrupt, but the set flag signals that corruption may have occurred.
You can remedy the situation by using the RepairDatabase method. This method inventories the objects in a database and checks each one for integrity. If there is no corruption, RepairDatabase will reset the write flag. If corruption has occurred then Jet will attempt to repair the database.
Before using this method, remember to make a backup of the database. Be certain that the database is not replicated, because RepairDatabase should not be used on a replicated database. If you do that, the database will no longer be replicable, nor will you be able to synchronize it with other replicas in the set. If a replica is corrupted, you should delete it and create a new replica from another in the set. The RepairDatabase method performs modification on certain system tables in the .mdb file. If the database were to remain a member of the replica set, the modification of the system tables would render the other replicas in the set corrupt. RepairDatabase prevents this from happening by not allowing the database to be replicated, thus preventing synchronization.
Compacting the database after it's repaired. RepairDatabase can recover truncated records of data, but not in Microsoft Jet 3.0 and 3.5 file formats. Repair these versions only if an error message indicates that this is necessary. In Microsoft Jet 3.0, if the RepairDatabase method or command is used before compacting, the database may not be able to open. While the problem of duplicate indexes on a database's system tables has been resolved in Microsoft Jet 3.5, version 3.0 still has this problem. There is an updated release of Jet 3.0 that corrects the problem. It is available here for users of Microsoft Access 95 and other programs that use Microsoft Jet 3.0.
About the Author
Barrie Sosinsky (firstname.lastname@example.org)is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- Microsoft Jet Database Engine Programmer's Guide, Second Edition
- The Best Database Backup and Recovery Web Links: tips, tutorials, scripts, and more.
- Have a backup or recovy tip to offer your fellow DBA's? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical backup and recovery questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our database design, SQL, Oracle, SQL Server, and DB2 gurus are waiting to answer your toughest questions.