f you use MS Access to store and manipulate data, you know that corruption happens. While it may not be an absolute certainly, Access is more susceptible to corruption than other databases. Because corruption in Access is usually the result of improper use, you can largely prevent it by simply avoiding the improper use practices. When corruption does occur though, the cause will be irrelevant?you’ll just want to recover as much data as possible.
Corruption comes in many forms, so it stands to reason that there are a number of ways to recover data. You could spend a lot of time trying one technique after another until something finally works, but pinpointing the actual type of corruption first will make the recovery more efficient. If the corruption is bad data, for example, you can usually recover almost all of the data. You will almost always have to re-enter the bad data, however. This article explains how to perform this data recovery when the worst happens.
Note that trying to recover data can actually compound your corruption problem. Before trying to recover the data, make a copy of the corrupted database. You read that right. That way, if something goes wrong, you’re no worse off than before you started. In addition, don’t try to recover data over a network; copy the .mdb file to a local system and work from there. Make sure you don’t backup the corrupted database to your latest working (and hopefully, still uncorrupted) backup, because doing so will corrupt the backup as well.
Start with the Easiest Fix
If you suspect you’re dealing with corruption, the best place to start is also the easiest. Access offers a built-in feature for repairing the database. So, cross your fingers and use this feature before doing anything else. If you’re lucky, it’ll work.
|Figure 1. Compact and Repair Database: Run Access’s built-in repair feature to resolve corruption problems.|
In pre-2007 versions of Access, you’ll find the Compact and Repair Database option on the Tools menu (usually a submenu deep, as shown in Figure 1). Use these version-specific instructions to initiate the repair process.
- Access 2003
- Choose Database Utilities from the Tools menu.
- Select Compact and Repair Database.
- Access 2007
- Click the Office button.
- Select Manage in the left pane.
- Choose Compact and Repair database.
- Access 2010
- Click the Office tab.
- Click Compact and Repair in the right panel.
In all versions, Access will close the database, clean things up a bit, and then reopen the database.
Bear in mind that this feature resolves specific Access-related issues. In 15 years of working with Access, I’ve yet to see it repair a corrupt table. That doesn’t mean it won’t work for you though, so definitely try it first.
Sometimes, importing tables into a new database resolves corruption (see Figure 2). The import (supposedly) forces Access to rebuild the table and indexes (indexes are notorious for corruption). This process also resolves corruption issues in non-table objects, such as forms.
|Figure 2. Import Data: Sometimes, importing all the objects and modules from the corrupt database into a new database will fix a corruption problem.|
Simply create a new database and import everything into it for testing. To import the old objects into the new database, use the appropriate version-specific instructions below.
- Access 2003
- Launch Access and create a new blank database.
- Choose Get External Data from the File menu.
- Select Import from the resulting submenu.
- Using the Import dialog box, locate the corrupt database, and click Import.
- In the resulting Import Objects dialog box (see Figure 2), click Select All, and click OK.
- Access 2007 and 2010
- Click the External Data tab.
- In the Import group, click Access.
- Click the Browse button in the resulting dialog box and locate the corrupt database. Click Open and then click OK.
- Using the Import dialog box, locate the corrupt database and then click Import.
- In the resulting Import Objects dialog box, click Select All and click OK.
- Ignore the prompt to save the import steps and click Close.
Bypass Memo Fields
Of all the Access database components, Memo fields are by far the most vulnerable to corruption. If you don’t really need them, either don’t use them or store them in a separate backend database. That way, corruption can’t crash your entire database.
If corruption occurs in a Memo field, you can recover most of the data as follows:
- Run an append query to copy all the records in the corrupt table into a new table, but do not copy the Memo field in the query. Copy everything but the Memo field.
- Run an update query to copy the Memo field values to the new copy.
As a general rule when you use this technique, Access will copy all but the corrupted Memo field values (but not always). At the very least, you’ll have all the records minus the text in the Memo fields.
Manually Copy Good Records
If you can open the table, review the last few records. If you see rows that contain inappropriate data, symbols, or pound signs (###), you’ve found the corrupt data. Simply copy the good records to a new table. (Don’t attempt to select the corrupted record. Doing so will generate an error.)
|Figure 3. Copy Table Structure: Sometimes, you can copy good data to a new table.|
First, you’ll need a new table with the same field structure. To copy the corrupted table’s structure only, take the following steps for all versions:
- Select the corrupted table in the Database window.
- Press Ctrl-C to copy the table.
- Press Ctrl-V to paste a copy of the table into the Database window.
- In the resulting Paste Table As dialog box, choose the Structure Only option (see Figure 3), provide a new name for the copy, and click OK.
Copying records one by one (if necessary) is feasible in a small table. However, if you can see the corrupt data, you can copy contiguous blocks of records instead. Simply select all the records above or below the offending record and copy the entire block as a single task.
When corrupt data isn’t visible, you can still use this block method. A block that contains corrupt data will complain just as if you’d selected the actual record. At that point, you can reduce the number of records comprising the block until you can pinpoint the exact record. When you’ve done so, simply select all the records above and below the offending record and copy them to the new table. Unfortunately, this technique isn’t practical in large tables.
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.