Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jul 17, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Repair, Compact, and Back Up Access MDB

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 tormey@foursquare.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
     GoTo ProcExit
    End If
    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
     GoTo ProcExit
   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
 Exit Function
  If Err = 53 Then 'Probably LDB not found
   Resume Next
  End If 'Err
   rv = False
  Resume ProcExit
End Function
Pat Tormey
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date