Solved: VBA Compact and Repair

[Jacob Hilderbrand]

Can someone tell me the VBA code to compact an Access database. This will be done from Excel.

I set it to do this automatically when it closes, but it doesn't seem to trigger when Excel opens and closes the database.

Thanks

Jacob

[mvidas]

Mr Jacob,

Have you tried using dbengine.compactdatabase?

untested:[vba] Dim Acc As Object
Set Acc = CreateObject("access.application")
Acc.DBEngine.CompactDatabase "C:\YourDB.mdb", "C:\tmp.mdb"
Acc.Quit
Set Acc = Nothing
Kill "C:\YourDB.mdb"
Name "C:\tmp.mdg" As "C:\YourDB.mdb"[/vba]Matt

[Jacob Hilderbrand]

Thanks

[stanl]

Not sure if this is for Office 2003 only, but I have used

 oACC = CreateObject("Access.Application")
 oACC.CompactRepair(LogFile:=True,SourceFile:=cMDB,DestinationFile:=cBkup)

Stan