MSACCESS.exe /Compact

[Psychic Psometimes]

I imagine that when I use the /Compact option in the command line of Access,
the program makes a copy, and compacts the copy; after the compacting is
finished, I'm guessing that , the program deletes the original and renames
the copy with the original name. I'm guessing that because it seems safer,
and the other compacting alternatives insist that you compact to a new file.

So, here's my question:
Can I depend on Access not to mess-up when doing that
(effectively compacting in place)?

I know that I can't be sure that
DBEngine.CompactDatabase DBName, TempDBName
will be finished when I go to delete DBName and rename TempDBName to (the
original) DBName.

Here is the code I'm using: Is this approach completely boneheaded?
(Note: I delay between. even though I know it will take more than 5 second
to compact; I merely want to permit Access to generate a unique temporary
file name.

Sub modMain()
'----------------------
CompactMDB "C:\Documents and Settings\Me\MyApp1.mdb"
Delay 5
CompactMDB "C:\Documents and Settings\Me\MyApp2.mdb"
Delay 5
CompactMDB "C:\Documents and Settings\Me\MyApp3.mdb"
End Sub
'----------------------
Sub CompactMDB(mdb_Name)
Quote = Chr(34)
Dim WshShell, oExec, oFileSysObj
Set WshShell = CreateObject("WScript.Shell")
Set oFileSysObj = CreateObject("Scripting.FileSystemObject")

strAccess = "D:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
If Not oFileSysObj.FileExists(strAccess) Then
strAccess = "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
End If
strCommand = strAccess & " " & Quote & mdb_Name & Quote & "/Compact"

Set oExec = WshShell.Exec(strCommand)
End Sub

 

[Douglas J. Steele]

While I think you can trust it, it's always healthy to worry about automated
processes!

My normal approach is to rename the file (typically by removing the .mdb
extension, putting in a date, then adding a .bak extension). I then compact
the renamed file to the name it should have. That way, I have backups. I've
also been known to put in code to delete say all but the last 3 backups.