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

This article was previously published under Q304561
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).

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.
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.
inf recover damaged corrupt corrupted table acc2000 acc97 access 2000 97

Article ID: 304561 - Last Review: 12/06/2015 04:26:26 - Revision: 2.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto KB304561