Need help to call Compact & Repair Database command from a office VB script

[news.microsoft.com]

I have a VB script in Excel that runs something like

SET db = opendatabase ("Filename")
db.querydefs ("Run query 1"
etc...

What I'm doing is running a query to delete the data in the table, drop the
indexes, load the data and then rebuild the indexes. I'd really like to
throw in a compact and repair database as well to avoid the 2 GB data limit
and the subsequent "invalid parameter limit". Can someone guide me how to
run the compact database command please? Thanks.

 

[Brendan Reynolds]

Public Sub CompactDatabase()

On Error Resume Next
dao.DBEngine.CompactDatabase "c:\usenet\test.mdb", "c:\usenet\temp.mdb"
If Err.Number = 0 Then
Kill "c:\usenet\test.mdb"
Name "c:\usenet\temp.mdb" As "c:\usenet\test.mdb"
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End If

End Sub

See 'CompactDatabase Method (DAO)' in the help file for more information.

 

[Brendan Reynolds]

BTW: Even though the code I posted kills the old MDB only if the compact
completes without error, I'd still recommend making a backup copy first.