Coding a macro to compact and repair several external databases

[DonD]

I have a macro that I want to use to compact several other databases. Each
line in the macro contains a RunApp command, with the following:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
"N:\Department\DATABASES\PurchaseOrders.mdb" /compact

The problem I'm having is that after the first one starts up, I get an error
message saying that the "database already exists". I'm assuming this is the
db1.mdb database that Access uses to compact.

Is there a way to designate the name of the temp database to use when
compacting, instead of it using the default db1.mdb? If I could have a
specific temp database name for each line in the macro, I could automate all
of my compacting chores.

The compact upon close option won't work for me.

Thanks for any help!

 

[Douglas J. Steele]

You might have to switch to VBA, and use the CompactDatabase method of the
Database object: it allows you to specify a from name and a to name.

What I typically do is rename the back-end, then compact it to the "proper"
name. In that way, I have a back-up in case something goes wrong:

' Check whether the backup already exists.
' If it does, delete it.
If Len(Dir("N:\Department\DATABASES\PurchaseOrders.bak")) > 0 Then
Kill "N:\Department\DATABASES\PurchaseOrders.bak"
End
' Rename the database.
Name "N:\Department\DATABASES\PurchaseOrders.mdb" As _
"N:\Department\DATABASES\PurchaseOrders.bak"
' Compact the renamed database to the proper name
DBEngine.CompactDatabase "N:\Department\DATABASES\PurchaseOrders.bak", _
"N:\Department\DATABASES\PurchaseOrders.mdb"