ACC: How to Troubleshoot/Repair Damaged Jet 3.0 and Prior Databases

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 on what can cause corruption is provided.

NOTE: This article only covers databases prior to Access 97. For additional information about troubleshooting databases in later versions of Access, click the article numbers below to view the articles in the Microsoft Knowledge Base:

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 Utility

The 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.

NOTE: 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 Utility

The 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 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 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.

• Reorganizes a table's pages so they reside in adjacent database pages. This improves performance since the table is no longer fragmented across the database.
• Reclaims unused space created by object and record deletions. When objects or records are deleted from the database, the space they occupied is marked as available for new additions to the database. However, the size of the database never shrinks unless the database is compacted. For databases in which objects and records are frequently added, deleted, and updated, you should compact frequently.
• Resets incrementing AutoNumber fields so the next value allocated will be one more than the highest value in the remaining records. For example, if all records in the database have been deleted, after compacting the database the value in the AutoNumber field will be 1 when the next record is added. If the highest remaining AutoNumber value in the database is 50, after compacting the value will then be 51 when the next record is added. Note that this is true even if records containing values higher than 50 were added previously but were deleted prior to compacting.
• Regenerates the table statistics used in the query optimization process. These statistics can become out-of-date over time, typically if transactions were rolled back, or if the database was not properly closed due to power loss or failure to completely exit the program using Microsoft Jet before turning the computer off.
• Flags all queries so that they will be recompiled the next time the query is executed. This is important because database statistics can change and a previously compiled query may have an inaccurate query plan.

Important Guidelines for Compacting a Database

Before 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. For more information on Microsoft Jet security, see Chapter 10, "Managing Security."

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 Database

NOTE: If 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: The following steps outline a general method for repairing a damaged database:

1. If you have Microsoft Access version 2.0, obtain the Microsoft Access 2.0 Service Pack if you are getting either of the following error messages in a multiuser environment:
   - Couldn't open SYSTEM.MDA
   -or-
   - <Database> is corrupt or is not a database file. Attempt to repair?
   In addition to fixing the problem mentioned above, the Service Pack also includes an enhanced Repair utility. For more information about the Service Pack, please see the following articles here in the Microsoft Knowledge Base:
   123589 ACC2: Error Message "Couldn't Open SYSTEM.MDA"
   123823 ACC2: MS Access Version 2.0 Service Pack Questions and Answers
   123588 ACC2: Microsoft Access Repair Utility Enhanced
2. Back up the damaged database (.mdb) file.
3. Delete the .ldb file if it is present. The corresponding .mdb file should be closed before deleting this file.
   
   The .ldb file is used to determine which records are locked in a shared database and by whom. If a database is opened for shared use, the .ldb file is created with the same name as the corresponding database (.mdb). For example, if you open (for shared use) the Northwind.mdb sample database in the c:\Msoffice\Access folder, then a file called Northwind.ldb is automatically created in the same folder. In Microsoft Access 7.0 and 97, the .ldb file is automatically deleted after the last user has exited the database with two exceptions--when the last user does not have delete permissions to the folder containing the .mdb file or when the database is corrupted.
   
   For more information about .ldb files, please see the following articles here in the Microsoft Knowledge Base:
   136128 ACC: Introduction to .ldb Files (95/97)
   109957 ACC: Introduction to .ldb Files (1.x, 2.0)
4. Run the Compact utility as follows:
   
   
   1. If a database is open, close it.
   2. Point to Database Utilities on the Tools menu, and then click Compact Database.
      
      In Microsoft Access 2.0 or earlier, on the File menu, click Compact Database.
   
   At this point, check your database to see if the damage has been repaired. If not, continue with the remaining steps.
5. If the damage is in a table, query, or index, run the Repair utility as follows:
   
   
   1. If a database is open, close it.
   2. Point to Database Utilities on the Tools menu, and then click Repair Database.
      
      In Microsoft Access 2.0 or earlier, on the File menu, click Repair Database.
   3. Select the damaged file in the Repair Database dialog box, and then click Repair.
   In Microsoft Access 2.0 or earlier, enter the name of the damaged file in the File Name box, and then click OK.
   
   If the repair is successful, you will receive the following message:
   
   In Microsoft Access for Windows 95:
   
   Microsoft Access repaired database '<path><database name>' successfully.
   
   In Microsoft Access 2.0 or earlier:
   
   Repair of database '<path><database name>' completed successfully.
   
   If the repair does not succeed, you will receive a message stating so. This means the damage to a table, query, or index is so severe that it cannot be corrected, or the damage is in an object that the Repair utility cannot affect.
6. If the damage is in a table, and the previous steps have not recovered it, try the following:
   
   
   1. In Microsoft Access, export the table to an ASCII (delimited text) file. For more information about this topic, search on the phrase "delimited text," and then view the "exporting Access data" topic using the Microsoft Access Help Index.
   2. Delete any relationships associated with this table, and then delete the table from the database.
   3. Compact the database.
   4. Re-create the table and any relationships it had.
   5. Using a word processor, examine the ASCII file for bad or strange data and remove those records. Save the file in an ASCII text file format
   6. Re-import the ASCII file into the newly re-created table. For more information about this topic, search on the phrase "delimited text," and then view the "importing or linking" topic using the Microsoft Access Help Index.
   7. Re-create any records you were forced to delete.
7. If the previous steps fail to recover your damaged database, try creating a new database and importing the objects, one-by-one, from the old database to the new one. Then re-create the relationships. This technique resolves problems with damaged system tables in the database.
8. If the damage is in a form or report, the damage can be either in the form or report itself or in one or more controls on the form or report. You can delete the form or report and import it from the backup copy of your database or use one of the following options:
   
   
   1. If the damage is in the form or report itself, create a new form or report, and then copy the controls from the original form or report.
   2. If the damage is in a control on the form or report, create a new form or report, and then re-create the controls on the new form or report. It is best to re-create all the controls, because there is no way to tell which controls are damaged.
9. If the damage is in a macro or module, the damage can be in the macro or module itself or in the contents of the macro or module. You can delete the macro or module and import it from the backup copy of your database or use one of the following options:
   
   
   1. If the damage is in the macro or module itself, create a new macro or module, and then copy the contents of the original macro or module.
   2. The damage could involve non-ASCII characters embedded in the module. Save the module as a text file, remove any bad or strange data, and then reload the text file into a new module.
   3. If the damage is in the contents of the macro or module, you must create a new macro or module, and then re-create the contents of the original macro or module.

NOTE: If you have Microsoft Access 95, consider downloading the utility Jetcomp.exe. Jetcomp.exe can in some cases repair databases that are otherwise unrecoverable. For information on prerequisite software needed to run jetcomp.exe and also how to obtain it, please see the following article in the Microsoft Knowledge Base: If you cannot repair the database with these steps, the database is probably damaged beyond repair. If this is the case, you should restore your last backup database or re-create the database.

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: For more information about the Microsoft Access Internet newsgroups, please see the following article here in the Microsoft Knowledge Base:

Typical causes of mdb file Corruption

There are three main causes of corruption in Access/Jet mdb files:

Database is Suspect/Corrupted Due to Interrupted Write Operation

Proper shut down, by clicking Exit or Close on the File menu, is highly recommended. However, 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 complete. 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 corrupt. In most cases, the data in the database is not actually corrupt, 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 Methods to determine which users and/or workstations are causing Jet to mark the file as suspect later in this article.

Faulty Networking Hardware

In this case, the file corruption does not involve the Jet 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 Program

There is no way to recover a .mdb file that was opened and then saved in a different program. For instance, Microsoft Word will allow you to open an Access database and then save it (By the way, it does not serve any purpose if you open a .mdb file in another application since all you see are extended characters). Saving it this way will cause the mdb file to prompt you for a database password when trying 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 Access checks when opening a file is where the database password would be. If that byte contains corrupt 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 thus unreadable to Access. Recovering a backup copy of the file is the only solution in this case. For additional information on this issue, click the article number below to view the article in the Microsoft Knowledge Base:

Steps that can be taken to prevent corruption

• Avoid losing power during database writes. Losing power can cause the database to be left in a suspect state.
• Avoid dropping network connections.
• Avoid abnormal termination of Microsoft Jet connections such as power loss, manual shutdown, having Task Manager shutdown the application, etc.
• When programming, close all DAO and ADO objects that you have open. Examples include Recordset, QueryDef, TableDef, and Database objects.
  
• Fatal system errors almost always cause abnormal termination. If your database is prone to fatal errors, you should resolve the errors before the database becomes too damaged to open or recover. For additional information on the Microsoft Jet Utilities, click the article number below to view the article in the Microsoft Knowledge Base:
  148424 ACC: Troubleshooting Fatal System Errors in MS Access 95 and 97
• Compact the database often.
• Do not run IPX on NT Server where Jet databases are located across the network and the client is Win95 with IPX/SPX. Instead run TCP-IP on the NT Server and a dual protocol stack of IPX and TCP-IP on the Win95 client. (NT to NT with IPX/SPX will not cause the problem, nor will Novell to any client.)
• Avoid Large Number of Open/Close Operations in a loop (40,000 successive open/close operations to over 1,000,000.

For more information about repairing databases, click Contents and Index on the Help menu, click the Index tab in Access Help, type the following text:

repairing

and then double-click the selected text to go to the Repair a damaged database topic.