Recently, a client started experiencing errors, seemingly at random, in the database application I managed for them. The errors immediately started mentioning corruption and then these came through …

Error 3167 – Record is Deleted

Getting everyone out of the system and running a Compact / Repair seemed to help, but then …

“The Microsoft Access database engine cannot find a record in the table … with key matching fields() …

At this point, I decided a rebuild of the database was in order since it hadn’t been done in years. I’d just create a new database file, import everything, test and everything should be resolved.

Then the import process failed on one of the main tables with the following error …

That told me where the corrupted data was at least; the problem was the table had over 110,000 records in it. It seemed to open just fine but any attempt to import, export or append the records through a query got the error above. I was able to copy and paste thousands of records at a time to a duplicate table but that didn’t tell me what records were corrupted and the client needed to know.

I decided to write a short VBA routine that would import the records one by one and report on any that failed to import as I knew that the corrupted record wouldn’t from my mass copy / paste experiment. I knew that with this many records, it might take a couple hours but I once saw an automated data migration that took weeks so I have certain perspective.

It should be rare that you ever really have to do something like this but the following code shows some of my thought process and the errors I anticipated.

The comments in the code should explain the flow pretty well but here are some highlights.

• The purpose of the recordset (rstOld) is simply to provide the primary keys to reference for each record. There’s no need to pull the entire record as I did at first.
• The DoCmd.SetWarnings False turns off the confirmations that action queries do (“You are about to insert 1 record …”) but it also turns off SQL error messages when a query fails. This is the reason for the separate DCount() command to verify the record is actually in the new table. The DCount(…) > 0 syntax returns a Boolean which determines the flow from there.
• There might be another way to actually copy the record but a SQL statement served well enough.
• Debug.Print sends any messages to the Immediate window in VBA which is enough for a process that is probably being run manually by the developer.
• The counter variable provides a handy way of letting the user see the progress, especially for a large table such as this one but the Mod test is essential. Printing every counter value to the console really would make the process take weeks.
• Do not forget DoEvents, at least if you don’t want to end up killing the Access process through the Task Manager to regain control. DoEvents allows the process to pause the necessary few milliseconds for the system to complete background tasks. You’ll still find Access sluggish but it will at least respond and you’ll be able to use the Reset button in VBA to break out of the process if you need to.
• Setting your recordset to Nothing cleans up the memory and is a good housekeeping step to remember.
• Errors don’t always have to end the routine. The Resume command sends execution back to the necessary point in the loop where the process can continue with the next record.

In the end, I didn’t have to recover the table in this way. The routine enabled me to identify the corrupt record which was obviously corrupt once I knew which one it was. As soon as I deleted that record, I was able to import the table straight into the new database file.