Compact Access Database from Excel via VBA

[SSweez]

I have the following code that I wish to execute from Excel that will
compact an Acess 2003 database. However I get an error that says
"Unrecognized Database Format." I have tried variations of text "MS
Access" such as "Acess" and "Microsoft Acess" without success. Does
anyone know the proper way to write this?

Sub test()
Dim db As DAO.Database
Dim str1 As String
Dim str2 As String
Dim str3 As String

str1 = "c:\db1.mdb"
Set db = OpenDatabase(str1, True, False, "MS
Access;password=password")
db.Close
str2 = Dir(str1)
str3 = Left(str1, Len(str1) - Len(str2)) & "temp.mdb"
DBEngine.CreateDatabase str1, str3
Kill str1
Name str3 As str1
End Sub

Thanks!

 

[datAdrenaline]

I have never tried this from Excel ... but I have used the following logic in
Access ... It *should* work in Excel too as long as you have the DAO
reference set ...

Public Sub sCompactDB(strDatabase As String)

Dim dbe As DAO.DBEngine

'Rename the database you wish to compact
Name strDatabase As strDatabase & ".cpk"

'Compact the database to the original name
dbe.CompactDatabase strDatabase & ".cpk", strDatabase

'Delete/Kill the UN-compacted file
Kill strDatabase * ".cpk"

End Sub

If you have a db password set, I beleive the syntax for compactdatabase is:
..CompactDatabase olddb, newdb, , , ";pwd=password"