ACC2000: How to Use MS Query to Recover Data from a Damaged Jet 4.0 Database (304561)



The information in this article applies to:

  • Microsoft Access 2000

This article was previously published under Q304561
Moderate: Requires basic macro, coding, and interoperability skills.

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

SUMMARY

This article describes how to use MS Query to recover data from tables in a Microsoft Access database when you cannot recover the database by using the methods described in the following articles:

306204 ACC2000: How to Troubleshoot Corruption in an Access Database

209137 ACC2000: How to Troubleshoot/Repair a Damaged Jet 4.0

247771 ACC2000: How to Recover Data from a Damaged (Corrupted) Table

Should the data be recoverable, you may be able to revert to a non-corrupted backup copy of the database and import the other Access objects.

MORE INFORMATION

Before you begin this process, keep the following things in mind:
  • Always test recovered databases before returning them to the production environment.
  • Do not delete the damaged database until recovery is confirmed.
To recover data from a damaged database table, follow these steps:
  1. Make a copy of the damaged database.
  2. Start Microsoft Excel.
  3. In a new workbook, click Data, point to Get External Data, and then click New Database Query.
  4. In the Choose Data Source dialog box, click New Data Source, and then click OK.
  5. In step 1 of the Create New Data Source dialog box, enter TestRecovery for the data source name.
  6. In Step 2 of the dialog box, select Microsoft Access Driver (*.mdb).
  7. In Step 3 of the dialog box, click Connect.
  8. In the ODBC Microsoft Access Setup dialog box, click Select.
  9. In the Select Database dialog box, browse to the problem database, and then click OK.
  10. In the Create New Data Source dialog box, click OK.
  11. In the Choose Data Source dialog box, you should now see the new database query, TestRecovery.
  12. Ensure that TestRecovery is selected, and then click OK.
  13. In the Query Wizard - Choose Columns dialog box, double-click the first table to add the fields to the Columns in your query section.
  14. Click Next through the wizard, and then click Finish.
  15. Excel then prompts you to specify where to insert the data. Accept the default of $A$1, and then click OK.
  16. Save the new Excel spreadsheet.
  17. Repeat steps 11 through 16 for each table in the database, and then import them to separate spreadsheets.
You can now import the individual spreadsheets into a non-corrupted backup copy of the database.

Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbhowto KB304561