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

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