RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Recovering Data from a Corrupt MS Access Table : Page 3

A corrupt table in MS Access means lost time and data. It can lead to a loss of revenue or even employment. Learn how you might be able to recover most of the data when the worst happens.


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 table—bypassing 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.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date