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:
ACC2000: How to Troubleshoot Corruption in an Access Database
ACC2000: How to Troubleshoot/Repair a Damaged Jet 4.0
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:
- Make a copy of the damaged database.
- Start Microsoft Excel.
- In a new workbook, click Data, point to Get External Data, and then click New Database Query.
- In the Choose Data Source dialog box, click New Data Source, and then click OK.
- In step 1 of the Create New Data Source dialog box, enter TestRecovery for the data source name.
- In Step 2 of the dialog box, select Microsoft Access Driver (*.mdb).
- In Step 3 of the dialog box, click Connect.
- In the ODBC Microsoft Access Setup dialog box, click Select.
- In the Select Database dialog box, browse to the problem database, and then click OK.
- In the Create New Data Source dialog box, click OK.
- In the Choose Data Source dialog box, you should now see the new database query, TestRecovery.
- Ensure that TestRecovery is selected, and then click OK.
- In the Query Wizard - Choose Columns dialog box, double-click the first table to add the fields to the Columns in your query section.
- Click Next through the wizard, and then click Finish.
- Excel then prompts you to specify where to insert the data. Accept the default of $A$1, and then click OK.
- Save the new Excel spreadsheet.
- 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.
Article ID: 304561 - Last Review: June 29, 2004 - Revision: 2.0
- Microsoft Access 2000 Standard Edition