Access 2007 Bug Alert: Compact and Repair Might Delete Your Database

The Access Team Blog has just posted this advice:

KB article 950812: Compact and repair might delete your database (Access 2007)

KB article 950812 provides more information about a scenario where some people have reported that a compact and repair deletes their database. You can’t download the fix directly but can get it by calling CSS [MS Customer Service?]. This fix will be included in SP2 when that ships (I don’t have an ETA). Here is the most relevant part of the article:

In Office Access 2007, when you try to compact an .accdb database, you may receive the following error message:

Microsoft Office Access can't delete database name.accdb after compacting it. The compacted database has been named Database.mdb.

If you compact a database using the same name, Microsoft Access creates a new compacted database and then deletes the original database. In this case, however, the original database wasn't deleted because it is read-only.

If you can, remove the read-only status, delete the original database, and then rename the new database using the original name.
If you can't remove the read-only status, inform your workgroup administrator.

Additionally, after you click OK, you receive the following error message:

Could not find file 'database name.accdb'

Then, the original .accdb database and the temp-compacted Databasex.mdb database are removed unexpectedly.

This potentially catasrophic scenario is something we need to be mindful of whenever attempting a Compact & Repair in Access 2007.

I suggest you the close database, and manually back it up, and then do the Compact & Repair on the closed file and not in the open database.

In a comment to the Access Team Blog post, Russell Sinclair said on May 29, 2008 3:35 PM:

Glad to see you've posted this to the blog. This one killed quite a bit of work for me. FYI, there is a work around I detailed at one point to a team member. When the error message comes up, DO NOT click OK. Instead go to the folder and create a copy of the “Database.mdb” file before Access has the chance to delete it. You can then safely click OK and Access will destroy the original Database.mdb database.

Update 2 June 2008: New Access 2007 Backup Before Compact Tool

I have responded to this issue with Backup Before Compact, a free Access 2007 Add-In that I have developed that when invoked automatically closes Access to unlock the database file, backups, and then compacts & repairs the database before re-opening it in Access 2007. A fail-safe feature renames the original file, which is retained as a temporary redundant backup until the tool is next used on the database.

You can download the Add-in from my Free Access Tools page.