Article ID: 279334 - View products that this article applies to.
This article was previously published under Q279334
Moderate: Requires basic macro, coding, and interoperability skills.
For a Microsoft Access 2.0/Jet 2.x and Access 95/Jet 3.0 version of this article, see 109953
For a Microsoft Access 2000/Jet 4.0 version of this article, see 209137
Environmental factors may leave your database file damaged. The symptoms of a corrupted database can range from #Deleted appearing in certain records, to the inability to open one of the objects in the database, to the complete inability to open the file in Access. The Repair and Compact utilities in Microsoft Access are useful tools for recovering and optimizing Microsoft Access database files. This article describes these utilities and offers additional alternatives for repairing damaged databases. Also, information about what can cause corruption is provided in the "Typical Causes of MDB File Corruption" section of this article.
Although the steps outlined in this article are usually successful in recovering damaged database files, to safeguard your data, you should create a backup copy of your database file as often as you can.
The Repair and Compact Utilities
The Repair UtilityThe Repair utility can fix databases with certain types of damage. It tries to repair only the tables, queries, and indexes in the database. It does not try to repair damaged forms, reports, macros, or modules; it does, however, copy them to the new, repaired database.
IMPORTANT: Run the Repair Database command only when the Microsoft Jet database engine returns an error message indicating that the Repair should be run. The Repair Database command should not be run under any other circumstances.
Compact UtilityThe Compact utility eliminates empty space in an existing database. It does so by creating a new destination database and copying each object in the old database to the new one. If you choose to compact the database into the original database name instead of to a new database, the Compact utility creates a temporary database, exports all the objects from the original database into the temporary database, removes the original database, and then renames the temporary database to the original database's name.
IMPORTANT: Compacting the database often is the best preventive maintenance for a .mdb file. The following is a list of actions the Compact utility performs.
Important Guidelines for Compacting a DatabaseBefore compacting a database, the following conditions must be met:
The user compacting the database must be logged on using an account that has Modify Design or Administer security permissions for all tables in the database.
Sufficient disk space must exist for both the original and compacted versions of the database, even if the database is being compacted through the Microsoft Access user interface using the same file name. The compacted database is renamed as the original database only when compacting is successful.
Other users must not have the database open. When a database is compacted, it has to be open exclusively by Microsoft Jet to prevent any users from accessing and modifying the database during the process.
Recovering a Damaged DatabaseIf you are experiencing Microsoft Jet 3.x error numbers 3197, 3343, or 3015, please also refer to the following article in the Microsoft Knowledge Base:
182867The following steps outline a general method for repairing a damaged database:
(http://support.microsoft.com/kb/182867/EN-US/ )ACC: Jet Database Engine 3.x Error Messages Due to Corruption
As a final alternative, some consultants may provide a Microsoft Access database repair service. Because this is such a specialized service, the most efficient way to locate a consultant is to post a message in the Microsoft Access "Third Party and User Groups" Internet newsgroup, which has the following newsgroup address:
microsoft.public.access.3rdpartyusrgrpFor additional information about Microsoft Access Internet newsgroups, click the article number below to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/150057/EN-US/ )ACC: Microsoft Access Newsgroups Available on the Internet
Typical Causes of MDB File CorruptionThere are three main causes of corruption in Access/Jet mdb files.
Database is Suspect/Corrupted Due to Interrupted Write OperationYou should always quit Access properly by clicking Exit or Close on the File menu. If a database is open and writing data when Access is abnormally shut down, the Jet database engine may mark the file as suspect/corrupted. This can happen if the computer is manually turned off without first shutting down Windows or if power is lost. Other situations may not shut down Access but may still interfere with Jet's writing of data to the disk while the database is open. This can happen, for instance when networks experience data collisions or disk drives malfunction. If any of these interruptions occur, then Jet may mark the database as potentially corrupted.
When Jet begins a write operation, it sets a flag, and resets the flag when the operation is completed. If a write operation is interrupted, the flag remains set. When you try to open that database again, Jet determines that the flag is set and reports that the database is corrupted. In most cases, the data in the database is not actually corrupted, but the set flag alerts Jet that corruption may have occurred. In cases such as this, compacting and/or repairing the database can typically restore the database. Fortunately, there are ways to determine which user and workstation was responsible for marking the file as suspect. See the "How to Determine What Users/Workstations Are Causing the File to be Marked Suspect" section later in this article.
Faulty Networking HardwareIn this case, the file corruption does not involve the Jet database engine; rather the file is literally corrupted by some outside cause. The cause can be one or more links in the hardware chain between the computer that the database resides on and the computer that has the database open. This list includes, but is not limited to, network interface cards, network cabling, routers, and hubs.
Hardware-based corruption is typically indicated by .mdb files that cannot be restored through the use of compacting, repairing, or Jetcomp. Hardware corruption will typically recur until the responsible hardware is repaired or replaced.
Opening and Saving the MDB File in Another ProgramThere is no way to recover a .mdb file that was opened and then saved in a program other than Access. For example, Microsoft Word allows you to open an Access database and then save it (although it serves no good purpose to do so because if you open an MDB file in another program, all you can see are extended characters). Saving the file in this manner will cause the .mdb file to prompt you for a database password when you try to open it in Access even though the file may have never been password-protected in Access. The password prompt occurs in such cases because the first byte range that Access checks when it opens a file is where the database password would be. If that byte contains corrupted data, Access treats the file as being password protected. Even if there were a way to get around the password prompt in this case, the database would still be unrecoverable because the binary structure is scrambled and therefore unreadable to Access. Recovering a backup copy of the file is the only solution in this case. For additional information about this issue, click the article number below to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/223043/EN-US/ )ACC: Database Password Appears Even Though It Was Never Set
How to Determine What Users/Workstations Are Causing the File to Be Marked SuspectYou can determine which workstation and user caused Jet to mark a .mdb file as suspect with the LDBView utility, which is available by downloading Jetutils.exe from the following Microsoft Web site:
http://download.microsoft.com/download/access97/utility1/1/WIN98/EN-US/JETUTILS.EXEFor additional information about the Microsoft Jet Utilities, click the article number below to view the article in the Microsoft Knowledge Base:
176670Ldbview let you observe which users are currently logged into the database, as well as any users that may have left the file in a suspect state.
(http://support.microsoft.com/kb/176670/EN-US/ )ACC: Microsoft Jet Utilities Available in Download Center
Steps That You Can Take to Help Prevent Corruption
Article ID: 279334 - Last Review: January 29, 2007 - Revision: 3.5