Mark,
Try this code. I typed some of it without testing so make sure to manually
backup test it before implementing on production systems.
Function Compact(stDataFile as String) as Boolean
'This function will backup and compact the mdb file given by the stDataFile
path.
'Manually backup and test debug it first!
Dim stOutFile as String, stBackUpFile as String
stOutFile = Left$(stDataFile, Len(stDataFile) - 4) & ".CMP"
DoCmd.SetWarnings False
DoCmd.Hourglass True
'Delete Temporary OutputFile if exists
On Error Resume Next
Kill stOutFile
On Error Goto Err_Function
'Backup
stBackUpFile = Left(stDataFile, Len(stDataFile) - 4) & ".BAK"
On Error Resume Next
Kill stBackUpFile
On Error Goto Err_Function
FileCopy stDataFile, stBackUpFile
'Compact
DBEngine.CompactDatabase stDataFile, stOutFile, DB_LANG_GENERAL
'Delete Uncompacted Version
Kill stDataFile
'Rename Compacted Version
Name stOutFile As stDataFile
Compact = True
Exit_Function:
DoCmd.SetWarnings False
DoCmd.Hourglass True
Exit Function
Err_Function:
Compact = False
msgbox err.description
Resume Exit_Function
End Function
HTH,
Josh
