Automating compact/Repair and backup of BE

[togo]

Hi I used the following code to attempt this:

strPathFilename_TemporaryBEDB, blnKeepBackup As Boolean) As Integer
' strPathFilename_OriginalBEDB is the path and filename of the
'      ACCESS file that you want to compact
' strPathFilename_TemporaryBEDB is the path and filename that
'      you want the function to use for the temporary copy of
'      the ACCESS file that the function will create as part
'      of how the function does the compacting (NOTE: if you
'      want to keep a copy of the backend file as a backup
'      (archive) copy, the function will add a date/time
'      stamp to the end of the filename)
' blnKeepBackup tells the function if you want to have it
'      keep a copy of the ACCESS file as a backup copy or not
'      (value of 0 or False tells the function to not keep
'      a copy of the compacted file as a backup copy; -1 or
'      True tells the function to keep a copy of the compacted
'      file as a backup copy)
' The function returns an INTEGER value:
'       -1     If a "lock file" (".ldb") exists for the original file, indicating that the
'                     file is in use (no compaction was done)
'        0     If no errors were encountered during the compaction process
'        1     If the original file cannot be found (no compaction done)
'        2     If an error was encountered during the compaction (no compaction done)
Dim intLocation As Integer
Dim xlngLooping As Long
Dim strTempBEDB As String, strTemp As String
Dim strDrive As String, strDateTime As String
Const strLockFileExtension As String = "ldb"
On Error Resume Next
strDateTime = Format(Now, "mmmddyyyyhhnnssAmPm")
strTempBEDB = strPathFilename_TemporaryBEDB
intLocation = InStrRev(strTempBEDB, "\")
strTempBEDB = Left(strTempBEDB, intLocation) & strDateTime & _
      Mid(strTempBEDB, intLocation + 1)
If Dir(Left(strPathFilename_OriginalBEDB, Len(strPathFilename_OriginalBEDB) - 3) & _
      strLockFileExtension) = "" Then
      On Error GoTo Err_Compact_1
      Name strPathFilename_OriginalBEDB As strTempBEDB
      DoEvents
      On Error GoTo Err_Compact_2
      DBEngine.CompactDatabase strTempBEDB, strPathFilename_OriginalBEDB
      DoEvents
      Do Until Dir(strPathFilename_OriginalBEDB) <> ""
            On Error Resume Next
            For xlngLooping = 0 To 25
                  DoEvents
           Next xlngLooping
      Loop
      On Error Resume Next
      If blnKeepBackup = False Then _
            Kill strTempBEDB
      CompactBackendDatabaseFile_Custom = 0
Else
      CompactBackendDatabaseFile_Custom = -1
End If
Exit_Compact:
      Exit Function

Err_Compact_1:
      On Error Resume Next
      MsgBox "The original database file cannot be found at this location:" & _
            vbCrLf & " " & strPathFilename_OriginalBEDB & vbCrLf & _
           "The file cannot be compacted.", vbExclamation, "Cannot Find The File!"
     CompactBackendDatabaseFile_Custom = 1
     Resume Exit_Compact

Err_Compact_2:
      On Error Resume Next
      Kill strPathFilename_OriginalBEDB
      FileCopy strTempBEDB, strPathFilename_OriginalBEDB
      MsgBox "An error occurred during the compacting operation of the file!" & _
            vbCrLf & _
            "The file cannot be compacted.", vbExclamation, "File Compaction Error!"
      CompactBackendDatabaseFile_Custom = 2
      Resume Exit_Compact
End Function

Firstly removed the 'as string' definitions from the header of the function since it didn't like the strings that I was passing:

Private Sub CloseSwitchboard_Click()
BEPath = "T:\Inspection Report Templates\Unified\Unified_be.accdb"
BEBackupPath = "T:\Inspection Report Templates\Unified\Backup\Unified_be(" & Date & " " & Time & ")"
Result = CompactBackendDatabaseFile_Custom(BEPath, BEBackupPath, -1)
End Sub

Now it gives me the error, "The original database file cannot be found at this location:". The path is good and filename is correct. What am I doing wrong? Thanks.

[June7]

Have you step debugged? Exactly which line triggers the error?

Are you sure the computer running code has T drive mapped? I don't use mapped drives in code if I can help it. I use UNC (Universal Naming Convention) which relies on server name instead.

\\servername\path\filename.accdb


Instead of Date & " " & Time try Now.