How to recover data from a damaged database table or a corrupted database table in Access 2000, Access 2002 or Access 2003 (247771)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000
This article was previously published under Q247771
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

This article describes how to repair a damaged or a corrupted Microsoft Access database when the database cannot be recovered by using the methods that are described in the following article:

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

209137 How to troubleshoot and repair a damaged Jet 4.0 database

This problem occurs when any one of the following conditions are true:
  • When you try to take any one of the following actions:
    • Run a query, a report, or a form
    • Update records
    • Scroll through data in a damaged database table
    When this problem occurs you may receive one or both of the following error messages:

    Error message 1
    Jet has stopped the process because you and another user attempted to change the same data at the same time.
    Error message 2
    #Error
  • When you use Jetcomp.exe to compact a database, you may receive the following error message:
    Records can't be read, no read permissions on MyDatabase.
    The MyDatabase placeholder is the name of your database.
  • You can open a damaged database table and view the data, but the damaged database table cannot be exported to another database or imported from another database.

MORE INFORMATION

Caution Create a copy of the damaged database to avoid causing irrecoverable damage during the repair process.

To recover data from a damaged database table, follow these steps:
  1. Make a copy of the damaged database table.
  2. Re-create or paste the structure only of the damaged database table to a new table.
  3. Open the damaged database table and the copy of the table structure so that you can see both tables.
  4. Switch to the damaged database table. On the View menu, click Datasheet View, and then select the whole record by clicking the record selectors that are the blank buttons to the left of each record. On the Edit menu, click Copy to copy the records from the damaged database table.
  5. Switch to the new table, put the pointer in a location that will put the data in the same location that the data was located in in the damaged database table. On the Edit menu, click Paste.
  6. Test the table after each copy-and-paste operation.
  7. Repeat steps 4 through 6 by using single records or small groups of records until you receive an error message while testing only the corrupted records that remain. Then, re-type any data that cannot be moved to the new table.

    Note You may be able to print the data from the damaged database table to make this step easier.
  8. After all records have been moved to the new table, create a new blank database in Access with a different name than the damaged database.
  9. Import the new table and all undamaged objects to the new database, and then quit Access.
  10. Rename the damaged database or move the damaged database to a new location. Delete the .ldb file for the corrupted database if the .ldb file exists, and then move the new database to the location that you want.
  11. Rename the repaired database to the name of the damaged database.
Notes
  • Always test a recovered database before you return the recovered database to the production environment.
  • Do not delete the damaged database until recovery is confirmed.
  • When you duplicate fields with the DataType property set to Auto Number, set the DataType to Number, and set the FieldSize property to Long Integer for the field in the recovered table. To revert them back to AutoNumber and still retain their original value, you have to create another table that is your final recovered table. Complete the previous steps to create the interim table by using Long Integer. Then, copy a new blank table by using Structure Only from the original table. Make sure to include the AutoNumber field. Use an Append query to append the good records from the newly recovered table. This correctly brings across the AutoNumber values to the newly recovered table.

REFERENCES

For additional information about recovering data from a Jet database in Access 2000, click the following article number to view the article in the Microsoft Knowledge Base:

304561 How to use MS Query to recover data from a damaged Jet 4.0 database

For additional information about troubleshooting and repairing a Jet database in Access 2003, click the following article number to view the article in the Microsoft Knowledge Base:

209137 How to troubleshoot and repair a damaged Jet 4.0 database

For additional information about troubleshooting a corrupted database in Access 2000, click the following article number to view the article in the Microsoft Knowledge Base:

306204 How to troubleshoot corruption in a Microsoft Access database

For additional information about the Jet Compact Utility for Access 2000, click the following article number to view the article in the Microsoft Knowledge Base:

273956 Jet Compact Utility available in Download Center

For additional information about sample code that you can use to import database objects in Access 2000, click the following article number to view the article in the Microsoft Knowledge Base:

298174 Sample code to import all database objects

Modification Type:MinorLast Reviewed:2/24/2006
Keywords:kbcorrupt kbRepair kbDatabase kbhowto kbinfo KB247771
©2004 Microsoft Corporation. All rights reserved.