Compacting

[Guest]

When I try and run code that compacts the database (Access 2000) I get an
error message that says I can't while code or macros are running. How then do
I compact from a switchboard menu option?

Thank you.

 

[Steve Schapel]

Wylie,

You can only compact a database if it is closed. If you are talking
about compacting a database from within itself, this can not be done
with a macro. It is possible to do it via a vba procedure that opens
another database, closes itself, the other database runs code to compact
the original, then open the ooriginal again and close itself. Another
option is to use the Compact On Close facility, via the
Tools|Options|General menu.

 

[tina]

AFAIK, you can't compact the current db from VBA, using

Application.CompactRepair

quote from the Help topic says

"The source file must not be the current database or be open by any other
user, since calling this method will open the file exclusively."

but you can compact from the switchboard form with a custom menu bar, custom
toolbar, or custom shortcut menu. just create the custom object and copy the
Compact and Repair option from the built-in menu Tools, Database Utilities.
note that this action will close, compact and reopen the database just as it
does when you select it from the built-in menu in the database window. you
don't have to have the db open exclusively, but you should be the only user
in it at the time.

hth

 

[hlam]

I have a problem in relating to this subject. I set the compact and repair
on close each time but a lot of times this gives me an error that the
compact cannot be completed. It creats a new database for me named db1.mdb
claiming unable to delete the original database. All I have to do is to
rename db1 to the original name. But if I call the compact from another
database there is no such a problem. Can anyone give me some advise please?
It is running on Access 2000.