Compact a database with VBA code

[mrtauntaun]

Hi all. I have a long macro (running from excel) that runs every night running a variety of reports out of an access database for me. The last thing I want the database to do is to compact. I know there is a command line you can use with the /compact option, but I can't figure out how to run that from vba. Is that the best way to do it? Is there an alternative?

I know about the option to have the database compact when it closes, but to save a long winded explanation, it doesn't work the way I need it to. I need the last line of code in the vba macro to compact the db.

Any help appreciated, thannks!

 

[AlexCuse]

I have used this before:

Public Sub CompactDB()

   CommandBars("Menu Bar"). _
   Controls("Tools"). _
   Controls("Database utilities"). _
   Controls("Compact and repair database..."). _
   accDoDefaultAction

End Sub

code is from

http://www.mvps.org/access/general/gen0041.htm

Hope it helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[PWise]

most probley your macro starts with something like

Dim appaccess As Access.Application
Set appaccess = CreateObject("Access.Application")
appaccess.OpenCurrentDatabase (PathAndFile)
......

add this line of code to the end of macro

appaccess.docmd.RunCommand acCmdCompactDatabase