compacting a database within a macro 1

[balllian]

I have got a delete query set up within a macro and after running this delete i want to follow it up with running a compacting of the database.

how can i do this as there doesnt seem to the option to do this within a macro??

Any help with this would be greatly appreciated.

Thanks

 

[BobStubbs]

Here is a function which you can paste into a module in your database. You can then run it from a command button, or a switchboard menu option:

Function CompactDatabase()

'----------------------------------------------------
'- Compact the database.  This only works if:       -
'- It is the only code in the function;             -
'- The function is called from the last line of     -
'- another VB procedure, or a Switchboard command   -
'----------------------------------------------------
   CommandBars("Menu Bar"). _
   Controls("Tools"). _
   Controls("Database utilities"). _
   Controls("Compact and repair database..."). _
   accDoDefaultAction

End Function

If you call this via a switchboard menu:

Text - Your choice, e.g. 'Compact The Database'
Command - Run Code
Function Name - CompactDatabase

I hope that this is useful.

Bob Stubbs

 

[balllian]

i have another couple of pieces of code within the database, will this cause this function not to work?

 

[BobStubbs]

There is no problem with other code elsewhere ... it is just that this piece of code must not be followed by anything else. For example, if you tried to follow the existing code with:

Msgbox "Compact process finished", vbInformation

... then it would not work.

An easy way to experiment with this, is to create a test form in your database. Put one command button on the form, then call the CompactDatabase function from the button's On_click event.

Bob Stubbs