Make sure you have a reference set to DAO. (With any code module open,
select Tools | References from the menu bar, scroll through the list of
available references until you find the one for Microsoft DAO 3.6 Object
Library, and select it.
Determine what the backend database name is. (Since you have a reference to
DAO set, this can be easily done by checking
CurrentDb().TableDefs("SomeTable").Connect, where SomeTable is the name of a
linked table in your database). For the sake of discussion, call this
strOldFile.
Replace the .MDB from the end of strOldFile with .LDB, and check whether or
not that file exists (Len(Dir$("C:\My Documents\MyFile.ldb")) will equal 0
if the file doesn't exist)
If the LDB file exists you can't compact the database, as it's in use.
Otherwise, come up with a new file name (a good approach is to add today's
date to the MDB's name: something like strNewFile = strOldFileWithoutMDB &
Format$(Date(), "yyyy-mm-dd") & .MDB).
Rename the backend database to the new file name you just created. (Name
strOldFile As strNewFile)
You can now compact the database using DBEngine.CompactDatabase strNewFile,
strOldFile
Once you know it's successfully compacted, you can either delete strNewFile,
or you can keep it as a backup. (My advice would be the latter)
