Compact on Close Danger

One of the pleasant surprises I've had writing this blog for the past year is how many smart and talented readers it's attracted.  One of those readers–Mark Burns–wrote in with the following advice (shared with permission):

The Dangers of Compact & Repair On Close

Please Note the following - all of you who favor doing a "Compact on Close" solution for MS-Access.

I used to prefer that option too, until one day I received the WORST error message possible from the DBEngine during a Compress & Repair on Close operation:

"Table MSysObjects is corrupt - Table Truncated."

Now, you have probably never realized that THAT error is even a possibility.

Well, it is….and if you ever see it during a Compress & Repair on Close operation, sorry, but your ENTIRE DATABASE, and EVERYTHING IN IT is now simply GONE. poof! (A quick explanation is in order here: because this error arises while Access is closing, by the time you see this error, it is almost certainly already too late to save the database by making a quick backup copy of the pre-c&r database file on disk because, since access is closing and exiting, the implicit series of steps - create a new database file; delete the previous one; rename the new one to the previous filename -  will in all likelihood be a fiat accompli before you can consider reacting to this and understanding what this error really means.)

What is funny about that is that Access will let you actually reopen the "fixed" database, only, the Access window and menu items (which generally depend on the various MSys* tables’ data to function) are typically now utterly useless (except to close the DB and exit access again) because ALL the tables (including the other MSYS* tables, forms, queries, reports, code modules, & macros) are simply gone - and with the disk space previously allocated to them released to the tender mercies of the Windows OS - unless you have additional protection than the bog-standard recycle bin, which generally won't help you either.

So, if you REALLY want to accept the risk of Compact on Close completely clobbering your database - with FEW POSSIBILITIES of recovering it, then please...do carry on.

If, OTOH, like me - you find that risk to your database an unacceptable one, well, don't enable C&R-on-Close - ever again.

I have never personally used the Compact on Close option for any of my Access databases.  I read early on in my Access development career that it was a bad idea (it may have even been one of Mark's posts that I read).  For that reason, I can't say how common a problem this is.

What I can say, though, is that in nearly 15 years of developing Access applications, I've never once felt like I needed the Compact on Close option.  

So, given the potentially catastrophic nature of a Compact on Close error, plus the fact that it provides relatively little benefit, I wholeheartedly agree that you should NOT enable the Compact on Close option in any of your Access applications.

Thanks for the tip, Mark!

P.S.  If you've got a similar Compact on Close horror story, let us know about it in the comments.  (Or email it to me at mike at nolongerset dot com and I'll post it for you anonymously.)

Image by LEEROY Agency from Pixabay