Corrupt Records in a Microsoft Access MDB

(Updated 2010/10/23)

[ Retrieve your data | Corrupt MDBs | Access | Main ]

Preliminary steps

Create a new MDB. Minimize the objects window as Access wastes significant time redisplaying that window for each new object.   Import all objects and relationships except for the table in question. Import just the definition of that table.

Locating damage records via scrolling

However one, or more records within a table may be corrupt. Especially memo and ole fields.  Scroll down the table until Access pukes.  Then start back a ways and slowly go down until you find the record which causes the problem.  Cut and paste those records into another table.  Then start from about thirty records, or however many records you can see on the screen plus a few, past the record which puked, scroll up a record at a time to ensure only one record has a problem and then keep on going down.

Locating damage via append queries as described by Peter Miller

If you can open the database, and simply can't resolve this problem with one table, your solution is relatively simple. Import just the table definition for the corrupt table (from the import dialog, make sure to choose the table definition only option rather than table def and data) into a new database. Then create a link to the corrupted table in the old database. Now create an append query to append all fields from the old table to the new table. Don't specify each one. Just use the tablename.* option. Now run the query. How far did it proceed without an error? Specify a key field in the query and limit it to a certain range of values. Exclude the key field value for the corrupted record(s). Your uncorrupted records will now be appended to the new database.

Locating damage via code

A third method to locate minor corruption, especially in memo fields is to write some code which loops through the records looking for an error when accessing the memo field(s).  

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tableName")

With rs
   Do While Not .EOF
      If IsError(!tableMemoField) Then
         Debug.Print !tableID
      End If
      .MoveNext
      Loop
End With

Debug.Print "Done"
rs.Close
Set rs = Nothing

(below added 2003-08-09)

The following procedure, while somewhat lengthy, will retain the autonumber value if you have child relationships to the table with corrupted memo fields.  If you don't have child relationships and don't care about the autonumber field then, using a query which shows all fields but the memo field, and cutting and pasting back in record(s) at the bottom of the table might work just as well.

Now what might work , and I haven't tried this myself as I don't have any tables with corrupted records, would be to add a checkbox to the table indicating a corrupt record. Delete all child relationships.

Use the above code to locate the corrupt records.  Modify the code to set the check box if desired. And if it will let you without causing an error.

Then run a make table query coping all the fields with the exception of the memo fields of the corrupt record(s) to another table. Run a delete query deleting those records. Run an append query copying the record(s) back in.  Recreate the child relationships.

How can I recover records from a corrupt table? Although I would change the code slightly to include the AutoNumber field, if available, in the error handling routine.

Finishing up once you've got clean records in the table in the new MDB.

Manually insert missing record(s) from a backup.

The layout of the relationships window will be all screwed up so use the SysRels utility appropriate for your version of Access to copy the table relationships window layout. Or use Save Restore Modify Relationship Window.

Recreate the relationships of that table.

Import all the non-corrupted objects (other tables, plus all queries, forms, reports, macros, modules, etc) to the new file, and you're set.  Mind you if you're running a proper split FE/BE system there should be very few other non table objects.

[ Retrieve your data | Corrupt MDBs | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available