Search code examples
vbams-accessbackup

Error 70 Permission Denied when attempting to Backup Database Backend

I am trying to automatically back up the backend of my split database which is located on a network drive. Unfortunately, I keep getting the error displayed in the title.

Code:

Function BackUpBE()

On Error GoTo Err_backup

Dim strNewBEname As String
Dim strOldBEname As String
Dim strDateStamp As String


strOldBEname = "P:\Access Datenbank\Durament_db_be\Durament_db_be.accdb"
'strOldBEname = "\\192.XXX.XX.XXX\Daten\Access Datenbank\Durament_db_be\Durament_db_be.accdb"

strDateStamp = Format(Date, "d.m.yy")

strNewBEname = "P:\Access Datenbank\Durament_db_be\BackUp\" & "Backup_vom_" & strDateStamp & ".accdb"
'strNewBEname = "\\192.XXX.XX.XXX\Daten\Access Datenbank\Durament_db_be\BackUp\" & "Backup_vom_" & strDateStamp & ".accdb"

'copy database
FileCopy strOldBEname, strNewBEname

MsgBox "The back-end database has been backed up!"

Exit_Backup:
Exit Function

Err_backup:
MsgBox Err.Number & Err.Description

Resume Exit_Backup
End Function

The code simply copies the current backend into another folder. At first I thought it was a server related issue concerning a password that is required. So I mapped the drive and used a local path, however, it still does not work. I have already stepped through the code using f8 and the error occurs upon exiting out of the function which does not make much sense to me. I appreciate any hints that would allow me to find the faulty part within my code, thanks in advance.

Solution

  • As @Andre suggested I managed to use FileSystemObject to copy the backend while the frontend is in use.

    Function BackUpBE()

On Error GoTo Err_backup

Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")

Dim strNewBEname As String
Dim strOldBEname As String
Dim strDateStamp As String

strOldBEname = "P:\Access Datenbank\Durament_db_be\Durament_db_be.accdb"
'strOldBEname = "\\192.XXX.XX.XXX\Daten\Access Datenbank\Durament_db_be\Durament_db_be.accdb"

strDateStamp = Format(Date, "d.m.yy")

strNewBEname = "P:\Access Datenbank\Durament_db_be\BackUp\" & "Backup_vom_" & strDateStamp & ".accdb"
'strNewBEname = "\\192.XXX.XX.XXX\Daten\Access Datenbank\Durament_db_be\BackUp\" & "Backup_vom_" & strDateStamp & ".accdb"

'copy current BE to Folder

Call fso.CopyFile(strOldBEname, strNewBEname)

MsgBox "The back-end database has been backed up!"

Exit_Backup:
Exit Function

Err_backup:
MsgBox Err.Number & Err.Description

Resume Exit_Backup

End Function