Problems with "Compact and Repair"

[LBryant]

This is a more general question, although I rely mostly on VBA for my Access design.

This issue only started recently and has me worried. I have a dabase named Media calendar.mdb. When I use the "Compact and Repair" utility, MS Access creates a new copy named "db1.mdb" and leaves the original file untouched.

This has never happened before. Can anyone tell me why this is happening? I do this manually as an admin function rather than always doing it on closing of the database.

What's going on? IT distributed updates to the MS Office suite. Could that have an effect?

[nico5038]

This happens when Access isn't able to save the compacted database under the "old official" name. A Compact&Repair always creates a temp database (db1.mdb) to store the result. Just check or the database does work and remove the old one and rename the new one.
You could also just try a new Compact and Repair, make sure that no objects are open when starting the compact & repair.

Nic;o)

[missinglinq]

And just to add, you really should always do it manually rather than using "Compact and Repair on Close!" This feature is one of Micro$oft's poorer ideas! Compacting can often cause corruption, and hence a backup copy of the database should always be made before compacting.

Welcome to bytes!

Linq ;0)>

[LBryant]

Well, my IT support got back to me with an explanation. It is indeed a result of a MS security patch. Here's what he sent me:

---------------------------------------------------------------------------------------
Issue: Access - Customer is trying to repair and compact his/her database and it creates a compacted db1.mdb file instead


Cause: This is likely due to an upgrade to Office 2003 11.0_3 and all pertinent security patches and compatibility packs

Resolution: The Microsoft (MS) Access features “Compact and Repair” and “Compact on Close” do not work when the MS Access database is on a clustered server and the version of MS Access is from the SMS package Office2003_11.0_3.0.0 (MS Office 2003 SP3).

A Microsoft provided hot-fix (KB945674) that corrects this problem is currently being evaluated and is tentatively scheduled to be included in the May 2008 Security patch release for Microsoft Office products.
------------------------------------------------------------------------------------

Hope this helps others working with Access 2003 in a corporate environment.

Leon

[NeoPa]

Thanks for this very helpful update Leon. We appreciate the feedback.

Although clearly in this case this is a result of the update, the more general advice given here is still worth following. It's unwise to rely too heavily on a Compact/Repaired database. I always do it from one file to a separate one then I can check the resultant database before deleting the original.

In practice I simply keep both copies and reuse the names next time around so I've always got a fallback.

PS. Sounds like your IT people are a reliable bunch. A fully researched answer like that is not always forthcoming.