RUNCOMMAND Repair and Compact?

[Geoff B via AccessMonster.com]

I have built a database for use by some less then Access Savy folks. Some of
the process I have built in delete and rebuild data from an outside SAS table.
This results in the database growing, I would like to build a button that
will run the Repair and Compact functions so that the I can keep the user out
of the menus. I found RUNCOMMAND function has both Repair and Compact, but
I get an error message that you cant run either when you have a macro running.


Is there another way?

 

[tina]

AFAIK, you can only run a compact on an open database from a menu bar option
(not shortcut menu). you could create a custom menu bar for your application
that only offers the Compact option.

NOTE: you can't compact an open database while more than one user is in it.
and with multiple users, your database should be split: tables in a backend
(BE) mdb on the server, and all other objects in a frontend (FE) mdb linked
to the BE tables, with a copy of the linked FE on each user's PC. running a
compact from the FE will compact the FE mdb only - it won't touch the BE,
which is presumably where the bloat is occurring as data is added/deleted
repeatedly.

suggest you look into setting up an automatic compact on the BE, to run
"after hours" when nobody is using the database. i can't give you specifics,
having never done it myself; but the question has been posed in the
newsgroups numerous times, so you might try doing a search in Google Groups
to find out more.

hth

 

[Steve Schapel]

Geoff,

If you are using Access 2000 or later, there is a Compact On Close
option available, on the General tab via the Tools|Options menu.

 

[Steve Schapel]

Geoff,

I also believe that a VBA procedure like this will work, though I have
never done it myself...

Application.CommandBars("Menu
Bar").Controls("Tools").Controls("Database utilities").Controls("Compact
and repair database...").Execute