Runtime error 70 (1 Viewer)

[mloucel]

Hello Gurus and Good Morning/Afternoon/Night:

I have a very simple program (my second one), I followed instructions to perform a Compact and Repair Database using VBA and a button
so I tried:

Dim Source As String, Dest As String, Result As Boolean

Source="C:\MyData\BE.accdb"
Dest="C:\MyData\Compacted.accdb"

'First Step Backup The Original to avoid any problems
FileCopy Source, Source & ".backup" & Format (Now(), "yyyymmdd")

'Delete dest file if exists

If Dir(Dest) <> "" Then Kill Dest

' Perform Compact
Result=Application.CompactRepair(Source, Dest, False)

If Result Then
    ' Success
    Kill Source
    Name Dest As Source
    Msgbox "Success"
else
    ' Fail
    Msgbox "Failed"
endif

PROBLEM:

Doesn't even run the backup I got a runtime error 70 or permission denied, I have like 30K hairs less, I tried to do it from a main menu, closing the database in case was open, but I have no success.

The CODE is not mine, I do not like to re-invent the wheel, was written by Richard Rost on his YouTube channel., I just modified it a bit to suit my needs.

Yes it works from the Database Tools [Menu] compact and repair option, no problem.

Any Ideas?

I am using Access 64, with Office 365 whatever is the latest.

 

[isladogs]

Always use Debug.Print to test output
Your path for the backup is incorrect. It will result in c:\MyData\BE.accdb.backup20230505 which is not a valid file path.
You need to split the source path so the .accdb is at the end. Also replace the dot before backup with an underscore

 

[mloucel]

Always use Debug.Print to test output
Your path for the backup is incorrect. It will result in c:\MyData\BE.accdb.backup20230505 which is not a valid file path.
You need to split the source path so the .accdb is at the end. Also replace the dot before backup with an underscore

Sorry been out for a few.. But the idea was to leave the date at the end so that nobody would use the database by mistake, I just need to copy the original to a different folder so that I have a backup but the same error persists [runtime error 70 or permission denied] and I have no choice but to do it from outside of the database with a batch file (yes good old dos)
Any Ideas on how can I accomplish this from within the database? [I am sort of newbie]
Thanks.

 

[Gasman]

Here is what I used to use
Supply a linked table name to GetAccessBE_PathFilename

Sub CreateBackup(Optional strDBType As String)
    Dim strDBpath As String, ext As String, tmp As String
    Dim strPath As String, strBackupPath As String, strDB As String
    
    
    'tmp = CurrentDb.Name    'or maybe this should be the name of your BE
    'strDBType = "FE"
    strDBpath = GetAccessBE_PathFilename("table1")
    strPath = Left(strDBpath, InStrRev(strDBpath, "\"))
    strBackupPath = strPath & "Backup\"
    
    'Will now backup front and back end database
    If strDBType = "FE" Then
        strDBpath = CurrentDb.Name
    End If
    strDB = Right(strDBpath, Len(strDBpath) - InStrRev(strDBpath, "\"))
    
    With CreateObject("Scripting.FileSystemObject")
        'ext = "." & .GetExtensionName(tmp)
        tmp = strBackupPath & Format(Now(), "yyyymmdd_hhnnss") & "_" & strDB
        .CopyFile strDBpath, tmp
    End With
    MsgBox strDBType & " Database saved as " & tmp
    
    
End Sub

Function GetAccessBE_PathFilename(pTableName As String) As String
'strive4peace

   ' RETURN
   '  the file path and file name of the BE database
   '  "" if the table is not linked
   
   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
      , tdf As DAO.TableDef
   
   GetAccessBE_PathFilename = ""
   
   Set db = CurrentDb
   Set tdf = db.TableDefs(pTableName)
   
   If Len(tdf.Connect) = 0 Then
      GoTo Proc_Exit
   End If
   
   ' look at Connect string - Database Type is the first thing specified
   ' if the BE is Access
   If InStr(tdf.Connect, ";DATABASE=") <> 1 Then
      GoTo Proc_Exit
   End If
   
   GetAccessBE_PathFilename = Mid(tdf.Connect, 11)
    
Proc_Exit:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetAccessBE_PathFilename"

   Resume Proc_Exit
   Resume
             
End Function

 

[mloucel]

I am going to try

Here is what I used to use
Supply a linked table name to GetAccessBE_PathFilename

Sub CreateBackup(Optional strDBType As String)
    Dim strDBpath As String, ext As String, tmp As String
    Dim strPath As String, strBackupPath As String, strDB As String
   
   
    'tmp = CurrentDb.Name    'or maybe this should be the name of your BE
    'strDBType = "FE"
    strDBpath = GetAccessBE_PathFilename("table1")
    strPath = Left(strDBpath, InStrRev(strDBpath, "\"))
    strBackupPath = strPath & "Backup\"
   
    'Will now backup front and back end database
    If strDBType = "FE" Then
        strDBpath = CurrentDb.Name
    End If
    strDB = Right(strDBpath, Len(strDBpath) - InStrRev(strDBpath, "\"))
   
    With CreateObject("Scripting.FileSystemObject")
        'ext = "." & .GetExtensionName(tmp)
        tmp = strBackupPath & Format(Now(), "yyyymmdd_hhnnss") & "_" & strDB
        .CopyFile strDBpath, tmp
    End With
    MsgBox strDBType & " Database saved as " & tmp
   
   
End Sub

Function GetAccessBE_PathFilename(pTableName As String) As String
'strive4peace

   ' RETURN
   '  the file path and file name of the BE database
   '  "" if the table is not linked
  
   On Error GoTo Proc_Err
  
   Dim db As DAO.Database _
      , tdf As DAO.TableDef
  
   GetAccessBE_PathFilename = ""
  
   Set db = CurrentDb
   Set tdf = db.TableDefs(pTableName)
  
   If Len(tdf.Connect) = 0 Then
      GoTo Proc_Exit
   End If
  
   ' look at Connect string - Database Type is the first thing specified
   ' if the BE is Access
   If InStr(tdf.Connect, ";DATABASE=") <> 1 Then
      GoTo Proc_Exit
   End If
  
   GetAccessBE_PathFilename = Mid(tdf.Connect, 11)
   
Proc_Exit:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
 
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetAccessBE_PathFilename"

   Resume Proc_Exit
   Resume
            
End Function

I am going to try this once I am in my office, coding is just seriously great.. Thanks ..

 

[Gasman]

You should be able to see that it will do both FE and BE.