Use a VBA Procedure to Copy Good Records
If VBA can copy records from one table to another for you, you won't have to spend a lot of time doing it manually. The VBA function procedure in Listing 1
(written for Access 2003) circumvents the corrupted data in much the same way as copying the records manually does (click here
to download a .bas
file of this procedure). If you can't open the table manually, or if there are just too many records to handle manually, you might be able to access the data programmatically with this technique. It doesn't always work, but the chance to automate the entire process makes it worth a try.
The VBA function procedure adds a Yes/No field named CopyFlag to the corrupted table, which might be a problem. Depending on the type of corruption, the table might not let the code add the field. If it doesn't, this method won't help you.
If the table does allow the procedure to add the field, the code then loops through the records in the corrupted table using an ADO recordset. When the record is accessible, the code adds the value -1 to the new field, CopyFlag. When accessing the record returns an error, the On Error Resume Next statement ignores that error and moves on to the next statement. A SQL query then copies all the records where CopyFlag contains the value -1 to a new tablebypassing the corrupt data. Finally, the code deletes CopyFlag from the new table.
A number of things could go wrong with this method. That's why there's no error-handling to speak of; you should take a hands on approach. If part of the code fails, make changes and try again.
You can find a number of similar procedures on the Internet. Most of them copy just a record's primary key value. These types of functions have a few limitations:
- The corrupt table must have a primary key. (Most will, but you occasionally will run into one that doesn't.)
- You must know how to rewrite the code to accommodate your table's primary key field.
- You must know how to (and remember to) run an update query to copy the remaining data from the corrupt table into the new table, relying on the primary key values.
When All Else Fails
Avoiding corruption in the first place is the best way to go. For specific tips about preventing corruption, read 10 Ways to Prevent Access Database Corruption
. When corruption does happen, revert to the most recent backup and rebuild. If that's not an option, try to recover data using the techniques in this article.
Despite your best efforts, however, you might not be able to recover enough (or any) data. When this happens, consider hiring an Access recovery expert or service. You can email the corrupt database to the expert or service, which will examine the database and reply with an estimate of how much can be recovered and what the recovery will costs.
You will also find a number of third-party, Access-recovery products that you can purchase and run yourself. If you decide to take this route, join a few online Access groups and ask for recommendations.