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 2

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.


Import Tables

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
    1. Launch Access and create a new blank database.
    2. Choose Get External Data from the File menu.
    3. Select Import from the resulting submenu.
    4. Using the Import dialog box, locate the corrupt database, and click Import.
    5. In the resulting Import Objects dialog box (see Figure 2), click Select All, and click OK.
  • Access 2007 and 2010
    1. Click the External Data tab.
    2. In the Import group, click Access.
    3. Click the Browse button in the resulting dialog box and locate the corrupt database. Click Open and then click OK.
    4. Using the Import dialog box, locate the corrupt database and then click Import.
    5. In the resulting Import Objects dialog box, click Select All and click OK.
    6. 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:

  1. 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.
  2. 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:

  1. Select the corrupted table in the Database window.
  2. Press Ctrl-C to copy the table.
  3. Press Ctrl-V to paste a copy of the table into the Database window.
  4. 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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date