ACC97: How to Repair a Damaged Jet 3.5 Database

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

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

• Reorganizes a table's pages so that they reside in adjacent database pages. This improves performance because the table is no longer fragmented across the database.
• Reclaims unused space that is 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 that the next value that is 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 because of power loss or failure to completely quit the program using Microsoft Jet before you turned the computer off.
• Flags all queries so that they will be recompiled the next time that the query is run. 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.

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

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. Make a copy of the damaged database (.mdb) file as a backup.
2. Make sure the .mdb file is closed and delete the corresponding .ldb file, if it is there.
   
   NOTE: 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:\Program Files\Microsoft Office\Office\Samples folder, then a file named Northwind.ldb is automatically created in the same folder. 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 additional information about .ldb files, click the article number below to view the article in the Microsoft Knowledge Base:
   136128 ACC: Introduction to .ldb Files (95/97)
   In troubleshooting what is causing database corruption, you can view the contents of .ldb files by running the Ldbview utility. For information about this utility, see the "How to Determine What Users/Workstations Are Causing the File to be Marked Suspect" section of this article.
3. Run the Compact utility as follows:
   1. If a database is open, close it.
   2. On the Tools menu, point to Database Utilities, and then click Compact Database.
      
      At this point, check your database to see if the damage has been repaired. If not, continue with the remaining steps.
4. If the damage is in a table, query, or index, run the Repair utility as follows:
   
   IMPORTANT: Run the Repair Database command only when the Microsoft Jet database engine returns a message indicating that the Repair should be run. The Repair Database command should not be run under any other circumstances.
   1. If a database is open, close it.
   2. On the Tools menu, point to Database Utilities, and then click Repair Database.
   3. Select the damaged file in the Repair Database dialog box, and then click Repair.
      
      If the repair is successful, you will receive the following message:
      Successfully repaired the '<path><database name>' database.
      If the repair does not succeed, you will receive a message stating so. This means that 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.
5. 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 file," 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 recreated table. For more information about this topic, search on the phrase "delimited text file," and then view the "importing or linking" topic using the Microsoft Access Help Index.
   7. Re-enter any records you were forced to delete.
6. 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.
7. If the damage is in a form or a report, the damage can be either in the form or the report itself or in one or more controls on the form or the report. You can delete the form or the report and import it from the backup copy of your database or use one of the following options:
   • If the damage is in the form or the report itself, create a new form or a new report, and then copy the controls from the original form or report.
   • If the damage is in a control on the form or the report, create a new form or a new 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.
8. If the damage is in a macro or module, the damage can be in the macro or the module itself or in the contents of the macro or the 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:
   • If the damage is in the macro or the module itself, create a new macro or a new module, and then copy the contents of the original macro or module.
   • 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.
   • 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.
9. If the corruption involves being unable to open the class module of a form or a report, or the design view of a module, you may want to try the /decompile command line switch. It is strongly recommended that you make a back-up copy of the database before attempting this method. The following is a sample command with the /decompile switch, wherein MSAccess.exe is in its default location and the database DB1.mdb is in the C:\ folder:
   C:\Program Files\Microsoft Office\Office\MSAccess.exe /decompile C:\DB1.mdb
   After running this command from the Run box, you are prompted to compile the database after using /decompile.
10. Consider downloading the utility Jetcomp.exe. Running Jetcomp.exe can in some cases repair databases that are otherwise unrecoverable. For additional information on how to obtain Jetcomp.exe, click the article number below to view the article in the Microsoft Knowledge Base:
    172733 Updated Version of Microsoft Jet 3.5 Available for Download

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 additional information about Microsoft Access Internet newsgroups, click the article number below to view the article 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

You 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 Hardware

In 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 Program

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

How to Determine What Users/Workstations Are Causing the File to Be Marked Suspect

You 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: For additional information about the Microsoft Jet Utilities, click the article number below to view the article in the Microsoft Knowledge Base: Ldbview 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.

Steps That You Can Take to Help 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, and so on.
• When programming, close all Data Access Object (DAO) and ActiveX Data Objects (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 about 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 Microsoft Access 95 and Microsoft Access 97
• Compact the database often.
  
  IMPORTANT: Do not run the Repair Database command in Access 97 unless Microsoft Jet prompts you to do so.
• Do not run IPX on Windows NT Server where Jet databases are located across the network and the client is Microsoft Windows 95 with IPX/SPX. Instead, run TCP-IP on the Windows NT Server and a dual protocol stack of IPX and TCP-IP on the Windows 95 client. Windows NT to Windows NT with IPX/SPX will not cause the problem, nor will Novell to any client.
• Avoid a large number of open and close operations in a loop (40,000 successive open/close operations to over 1,000,000.