ACC: How to Repair a Damaged Database

ID: Q109953

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

Environmental factors, such as power outages and power fluctuations, improper closing of Microsoft Access, and disk or network failures, may leave your database file damaged. The Repair and Compact utilities in Microsoft Access are useful tools for recovering and optimizing Microsoft Access database files. This article describes how to use these utilities and additional alternatives for repairing a damaged database file.

MORE INFORMATION

Although the steps outlined in this article are usually successful at recovering damaged database files, to safeguard your data, you should create a backup copy of your database file as often as is feasible for you.

The Repair Utility

You can use the Repair utility to fix databases with certain types of damage. The Repair utility 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.

Recovering a Damaged Database

The following steps outline a general method for repairing a damaged database:

If you are using 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:

Q123589 ACC2: Error Message "Couldn't Open SYSTEM.MDA"

Q123823 ACC2: MS Access Version 2.0 Service Pack Questions and Answers

Q123588 ACC2: Microsoft Access Repair Utility Enhanced

Back up the damaged database (.mdb) file.

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:

Q136128 ACC: Introduction to .ldb Files (95/97)

Q109957 ACC: Introduction to .ldb Files (1.x, 2.0)

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.

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

Successfully repaired the '<path><database name>' database.
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.

If the damage is in a table, and the previous steps have not recovered it, try the following:
1. From within 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. Recreate 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," and then view the "importing or linking" topic using the Microsoft Access Help Index.
7. Recreate any records you were forced to delete.

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 recreate the relationships. This technique resolves problems with damaged system tables in the database.

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:
- 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.
- If the damage is in a control on the form or report, create a new form or report, and then recreate the controls on the new form or report. It is best to recreate all the controls, because there is no way to tell which controls are damaged.

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

NOTE: If you use Microsoft Access 95 or Microsoft Access 97, consider downloading and using the utility Jetcomp.exe. Jetcomp.exe can in some cases repair databases that are otherwise unrecoverable. The utility is included as part of the Microsoft Jet 3.5 update.

For information on how to obtain Microsoft Jet 3.51, please see the the following article in the Microsoft Knowledge Base:

Q172733 ACC97: Updated Version of Microsoft Jet 3.5 Available on MSL

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

microsoft.public.access.3rdpartyusrgrp

For more information about the Microsoft Access Internet newsgroups, please see the following article here in the Microsoft Knowledge Base:

Q150057 ACC: Microsoft Access Newsgroups Available on the Internet

REFERENCES

For more information about repairing databases, type "repairing" in the Office Assistant, click Search, and then click to view "Repair a damaged database."

Additional query words: corrupt corruption


Keywords          : kbusage GnlCmp 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 2, 1999