By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,337 Members | 2,084 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,337 IT Pros & Developers. It's quick & easy.

Problems with "Compact and Repair"

P: 18
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?
May 12 '08 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,072
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.

May 12 '08 #2

Expert 2.5K+
P: 3,532
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)>
May 12 '08 #3

P: 18
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.

May 13 '08 #4

Expert Mod 15k+
P: 31,494
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.
May 13 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.