Private Sub Command0_Click()
On Error GoTo Err_backup
    
Dim strFullPath As String
Dim strBackendFile As String
Dim strPath As String
Dim strSourceFile As String
Dim strDestinationFile As String

' obtiene la ruta del back-end usando una tabla vinculada como referencia.
' La función MID elimina la información de conexión, incluida la contraseña

strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("Actual_Volume").Connect, 1)

'MsgBox (strFullPath)

' aisla el nombre de la base de datos backend
    For i = Len(strFullPath) To 1 Step -1
        If Mid(strFullPath, i, 1) = "\" Then
            strBackendFile = Mid(strFullPath, (i + 1))
            Exit For
        End If
    Next

    For N = Len(strBackendFile) To 1 Step -1
        If Mid(strBackendFile, N, 1) = "." Then
            strBackendFile = Left(strBackendFile, (N - 1))
            Exit For
        End If
    Next

' elimine el nombre de archivo de la base de datos para aislar la ruta
    For i = Len(strFullPath) To 1 Step -1
        If Mid(strFullPath, i, 1) = "\" Then
            strPath = Left(strFullPath, i)
            Exit For
        End If
    Next

' reconstruye las rutas de archivo de origen y destino
'al archivo de destino se incluye el día abreviado de la semana
' \backup\ este directorio debe de existir

strSourceFile = strPath & strBackendFile & ".accdb"
strDestinationFile = strPath & "backup\" & strBackendFile & "-" & WeekdayName(Weekday(DATE), True) & ".accdb"
FileCopy strSourceFile, strDestinationFile
MsgBox "The back-end database has been backed up.", vbOKOnly, "Success"

Exit_Backup:
Exit Sub

Err_backup:
If Err.Number = 0 Then
    ElseIf Err.Number = 70 Then
        MsgBox "The file is currently in use and therefore is locked and cannot be copied at this time. Please ensure that all forms, reports, and queries are closed, and that no one is using the database and try again.", vbOKOnly, "File Currently in Use"
    ElseIf Err.Number = 53 Then
        MsgBox "The Source File '" & strSourceFile & "' could not be found. Please validate the location and name of the specifed Source File and try again", vbOKOnly, "File Not Found"
    Else
        MsgBox "Microsoft Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Error Source: ModExtFiles / CopyFile" & vbCrLf & "Error Description: " & Err.Description, vbCritical, "An Error has Occured"
End If

Resume Exit_Backup

End Sub

    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("Actual_Volume").Connect, 1)

    ' extraemos nombre del back end
    strBackEndFile = Dir(strFullPath)
    
    ' extraemos ruta del back end
    strPath = Replace(strFullPath, strBackEndFile, "")
    
    ' quitamos la extensión al nombre del back end
    strBackEndFile = Replace(strBackEndFile, ".accdb", "")

    ... aquí el resto del código

strDestinationFile = strPath & "backup\" & strBackendFile & "-" & WeekdayName(Weekday(DATE), True) & ".accdb"

Debug.Print strSourceFile
Debug.Print strDestinationFile

MS Access;PWD=1234;DATABASE=C:\Apps\My_db\Datos\MART_BackEnd.accdb
MS Access;PWD=1234;DATABASE=C:\Apps\My_db\Datos\backup\MART_BackEnd-Wed.accdb

    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("Actual_Volume").Connect, 1)

    ' extraemos nombre del back end
    strBackEndFile = Mid(strFullPath, InStr(strFullPath, "DATABASE=") + 9)
    strBackEndFile = Dir(strBackEndFile)
    
    ' extraemos ruta del back end
    strPath = Replace(strFullPath, strBackEndFile, "")
    
    ' quitamos la extensión al nombre del back end
    strBackEndFile = Replace(strBackEndFile, ".accdb", "")

' resto del código

strDestinationFile = "C:\Apps\My_db\Datos\backup\" & strBackendFile & "-" & Format(DATE, "ddmmyyyy_hhnnss") & ".accdb"
Debug.Print strDestinationFile

strDestinationFile = CurrentProject.path & "\Datos\backup\" & strBackendFile & "-" & Format(Now(), "ddmmyyyy_hhmmss") & " .accdb"