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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q247771
This article has been archived. It is offered "as is" and will no longer be updated.
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
Properties

Article ID: 247771 - Last Review: 12/05/2015 17:45:10 - Revision: 4.4

Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition, Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbcorrupt kbrepair kbdatabase kbhowto kbinfo KB247771
Feedback