Recently, a customer started experiencing seemingly random errors in a database application that I managed for him. The mistakes began immediately with the mention of corruption, and then they appeared…
Error 3167 – the record was deleted.
Pulling everyone out of the system and running Compact/Repair seemed to help, but then…
The Microsoft Access database engine does not find an entry in the table … with important comparison fields() …
At that moment I decided that the reconstruction of the database was acceptable, because it hadn’t been done for years. I just create a new database file, import everything, check it and everything should be allowed.
Then the import process failed on one of the main tables with the following error …
Booking error (-1524); no error message is displayed.
At least that tells me where the corrupt data is; the problem is that the table contains more than 110,000 records. It appears to open, but any attempt to import, export, or add entries through a search has received the error described above. I could copy thousands of records at a time and paste them into a double table, but it didn’t tell me which records were corrupt and the customer had to know.
I decided to write a short VBA routine that would import the inputs one by one and report all inputs that could not be imported, knowing that the corrupt input would not come from my mass copy/paste experience. I knew it could take a few hours with so many files, but one day I saw an automated data migration that lasted several weeks, so I have some perspective.
It’s rare that you really have to do something like this, but the following code shows some of my thoughts and the mistakes I expected.
The remarks in the code should explain the thread well, but here are a few excerpts.
- The purpose of Recordset (rstOld) is simply to provide the primary keys to refer to each record. You don’t have to take out the whole record like I did in the beginning.
- DoCmd.SetWarnings False disables confirmations that create action queries (you insert 1 record…), and also disables SQL error messages if the query fails. Therefore, a separate DCount() command to check the record is included in the new table. The syntax DCount(…) > 0 returns a boolean, which determines the wire from there.
- There may be another way to copy a record, but the SQL query has served you well enough.
- Debug.print sends all messages from the pop-up window to the VBA, which is sufficient for a process that can be performed manually by a developer.
- A variable counter provides a convenient way for the user to see progress, especially with an array as large as this one, but the mod test is very important. Printing out every counter reading on the console will really take weeks.
- Don’t forget the DoEvents, at least if you don’t want the task manager to interrupt the access process at the end to regain control. With DoEvents, you can interrupt the process for a few milliseconds, which the system needs to perform tasks in the background. You will notice that the access is still slow, but at least it responds, and you can use the Reset button in VBA to end the process if necessary.
- Setting up Recordset to Nothing wipes away memory and is a good step to remember in the household.
- Mistakes don’t always have to end. The Continue command returns the execution to the desired loop point where the process can be continued with the next dataset.
After all, I didn’t have to set the table like this. Thanks to this routine, I was able to identify the damaged recording, which was clearly damaged as soon as I found out which recording was which. After deleting this entry, I was able to import the table directly into the new database file.
repair corrupt access database,access decompile,id is not an index in this table,access database repair tool freeware,microsoft access recover deleted records,jet compact utility,kernel for access database repair,repair access database 2007,extract vba code from corrupt access database,recover vba code from corrupt access database,stellar repair for access,the database cannot be opened because the vba project contained in it cannot be read,best access database repair tool,the database can't be repaired or isn't a microsoft access database file,compact and repair access database from command line