Thursday, April 21, 2011

Don't Be THAT Guy

This one will be short. A few weeks ago I decided to update an old application I created a few years ago for a friend. It was created with Access 2002. The new version is in Access 2010 and it showcases a few 2010 specific features, as well as an updated table structure. I worked on it off and on over a couple of weeks. One night I was tweaking a form when Access threw an error, informing me it had experienced a problem and had to close. It allowed me to make a backup of the file and closed gracefully. That's not too uncommon, and most of the time recovery means finding the problem and correcting it before moving on.

This time, when I opened the database, Access greeted me with the following dialog.

Notice that there are two choices (not counting "Help"):
  • Cancel, which closes the database so you can make new back up copy of the corrupt file.
  • Delete all of the VBA.
I took a long, slow, calming breath, repeated to myself my new Access mantra, "Go with the Zen of Twenty Ten", got up and went to the kitchen for a fresh cup of coffee, and finally returned to ponder my options. Close the database or delete ALL of the code in it.

I won't bore you with the rest of the story, which involved attempts at recovery options from  Tony Toewes Discussion of Corruption and  Allen Browne's Discussion of Corruption. It was no use. Even the ultimate last resort, "SaveAsText" to export the corrupted form was not possible because I couldn't open the immediate window.

At the end of that night I had a whole new accdb. Only the tables and queries could be imported into the new accdb.  Over the next couple of weeks I had to recreate all of the forms, reports and code modules.

Some of you are probably shouting at your monitor at this point, "Why didn't you just restore one of your backups?" Well, the backup turned out to be the orginal mdb from which I started. Not much use, as it turned out.

Well, all I can say is that I won't the THAT guy ever again. Don't you be that guy either, okay?