VB code to compact

[Eric G]

Is there VB code I can use to compact by clicking on a form button?
I'd prefer not to have compact/repair available to users from the
regular menu.

TIA Eric

 

[jamie considine]

docmd.RunCommand acCmdCompactDatabase

 

[Eric G]

Great, thanks a lot Jamie.

Eric

docmd.RunCommand acCmdCompactDatabase

 

[Eric G]

Hi Jamie,

I guess I spoke too soon.

When I tried attaching the code you suggested to a command button on a
form I received the following error msg:

"You can't compact the open database while running a macro or VB
code"
"Use the tools menu instead"

Any ideas? Eric

 

[Andy Cole]

Hi Eric

Use the Option to Compact on close, Tools > Options > General Tab instead

HTH

Andy

 

[Michel Walsh]

Hi,


http://www.trigeminal.com/, TSI utilities, search for TSI SOON.


Hoping it may help,
Vanderghast, Access MVP

 

[Eric G]

Hi Andy,

Is this the best way to go with approx. 10 front-end users per day on
the database?
I was thinking of compacting once at the end of the day myself, but if
it won't be a drag on closing and is better for the db I'll use the
method.

Thanks! Eric

 

[Andy Cole]

Hi Eric

If you're going to use a FE-BE split ( and I recommend you do with 10 Users)
then using the Options setting will only compact the FE. I've found that it
is rarely necessary to compact/repair the FE unless the app is going to
create temporary tables etc in the FE. The problem with calling the Menu
item in code is that it won't work (as you've found) while code is running
as compact/repair need to close the DB. I suspect this is why MS added the
Options setting in A2K. Compact/Repairing the BE can be done with code from
the FE DB but must be done when no one is connected to the BE. Another
option is to set-up a Windows scheduled task to compact the BE when no one
is using it does mean that one machine will need to be left on to run
this). This can be done using the command line option, /compact (which in
A2K includes an automatic /repair as well).

HTH

Andy

 

[Eric G]

Hi Andy,

Another option is to set-up a Windows scheduled task to compact the BE when no one
is using it does mean that one machine will need to be left on to run
this). This can be done using the command line option, /compact (which in
A2K includes an automatic /repair as well).

This sounds like the best way to go. So I just schedule a Windows task
to run say, Det.mdb /compact ? Will this leave the db open or just
compact it and keep it closed?

Thanks! Eric

 

[Eric G]

Thanks a lot Andy. We won't be using Access security so the shortened
version should work out perfectly.

Eric