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

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

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.
 
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.
 
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.
 
Back
Top