Coding a macro to compact and repair several external databases

  • Thread starter Thread starter DonD
  • Start date Start date
D

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!
 
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"
 
Back
Top