This repair and compact also backs up the Access MDB.
Function ValidateMdb(MDB As String) As Boolean
This function repairs, compacts, and backs up a database file
and deletes stray LDB (locking files) as it goes. The concept is to copy the file to a BAK version, then repair
and compact the BAK back to the original. The renaming keeps it from
being opened before you get a change to compact it, and it provides a backup since the client doesn't do that. I call it Validate because NOBODY wants to wait for a file to be
backed up or compressed but everybody wants their data validated!
'Dim bSuccess As Boolean
'bSuccess = ValidateMdb("c:\my documents\db2.mdb")
Pat Tormey email@example.com
Dim dbe As New DBEngine 'Set Ref to MS DAO
Dim sLdb As String 'Locking file's Name
Dim sBak As String 'BackUp file's Name
Dim rv As Boolean 'My standard for a return variable
On Error GoTo ProcErr
rv = False
If Len(Dir(MDB)) = 0 Then 'No File Found
sLdb = Left$(MDB, InStr(MDB, ".")) & "LDB"
sBak = Left$(MDB, InStr(MDB, ".")) & "BAK"
Kill sLdb 'delete it if you can
If Dir$(sLdb) <> "" Then 'is it still here? Still in Use
End If 'Locked
If Dir$(MDB) <> "" Then 'this is to be sure I don't delete the BAK
Kill sBak if the MDB isn't around
Name MDB As sBak 'Prevents somebody else from opening it
End If 'Is there an MDB
dbe.RepairDatabase sBak 'repair the backup
dbe.CompactDatabase sBak, MDB 'compact back to original
rv = True
ValidateMdb = rv
If Err = 53 Then 'Probably LDB not found
End If 'Err
rv = False