VB code to compact

  • Thread starter Thread starter Eric G
  • Start date Start date
E

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
 
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
 
Hi Eric

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

HTH

Andy
 
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
 
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
 
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).
Click to expand...

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
 
Thanks a lot Andy. We won't be using Access security so the shortened
version should work out perfectly.

Eric
 
Back
Top