Microsoft KB Archive/815280

Knowledge Base

ACC2000: How to Recover Data from a Corrupted Table by Using the DAO Method

Article ID: 815280

Article Last Modified on 10/14/2003

---

APPLIES TO

• Microsoft Access 2000 Standard Edition

---

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

Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

This article describes how to recover data from a corrupted table in an Access database. You can do this by using the Microsoft Data Access Objects (DAO) db.Execute method. You can use the db.Execute method when you cannot recover your data by using either MS Query or the Compact and Repair Database utility in Access.

MORE INFORMATION

Note The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

To recover the data from the corrupted table in Access, follow these steps:

1. Make a copy of the corrupted database. Exclude the corrupted tables. Name the database Recovered_Database.
2. Open the Access database that contains the corrupted tables.
3. Click Modules and then click New.

4. Put the following code in the new module:
   
   Note The following code creates a table in Recovered_Database with the same name as the corrupted table (<Corrupted_Table>).

   Sub Recovery()
   
       Dim db As Database
       Dim sql As String
   
       ' Set the database to current database.
       Set db = CurrentDb()
   
       ' Set sql to a string that represents the query to recover data from
       ' the corrupted table.  <Corrupted_Table> is the name of corrupted table.
       sql = "SELECT <Corrupted_Table>.* INTO <Corrupted_Table> in " & _
         "'<Absolute_Path_of_Recovered_Database>\Recovered_Database.mdb'" & _
         " FROM <Corrupted_Table>"
   
       db.Execute sql
   
   End Sub

5. On the File menu, click Save. Save the module as Module1.
6. On the Run menu, click Run Sub/UserForm.
7. Repeat step 3 to step 6 for each corrupted table.

After you complete these steps:

• Test the recovered database before you return the database to the production environment.
• Do not delete the damaged database until you confirm the recovery.

REFERENCES

For additional information about troubleshooting the corrupted database and how to recover data from a corrupted table, click the following article numbers to view the articles in the Microsoft Knowledge Base:

306204 ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database

209137 ACC2000: How to Troubleshoot and Repair a Damaged Jet 4.0 Database

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

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

Additional query words: Recovery corrupt corrupted table database Acc2000 recover

Keywords: kbhowto KB815280

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.