FileSystemObject sub getting run-time error

[Glenley]

My tables are hosted on SharePoint and I have a series of Make Table Queries that I run onto a blank DB file for my nightly backups. I'm trying to run a subroutine that will copy that DB file and store the copy into a folder named May22 for this month and June22 for next month etc... but when I run it, I get a Run-Time error '70: Permission Denied. What am I doing wrong here? I have the Microsoft Scripting Runtime library turned on. I'm at a bit of a loss

    Private Sub btnBackupTables_Click()

    Dim FSO As New FileSystemObject
    Dim FilePath As String, FileDest As String
    Dim FolderName As String
    
    FolderName = Format(Date, "mmmm") & Format(Date, "yy")
    FilePath = "C:\Users\bogle\OneDrive - aavcor.com\Documents\DB Backups\BackupsFromQuery_aavdb.accdb"
    FileDest = "C:\Users\bogle\OneDrive - aavcor.com\Documents\DB Backups\" & FolderName
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "CustomerTBackupQ"
    DoCmd.OpenQuery "DonorTBackupQ"
    DoCmd.OpenQuery "EmployeeTBackupQ"
    DoCmd.OpenQuery "LabTBackupQ"
    DoCmd.OpenQuery "MROTBackupQ"
    DoCmd.OpenQuery "OfficerEmployerTBackupQ"
    DoCmd.OpenQuery "ReasonForTestTBackupQ"
    DoCmd.OpenQuery "TestTBackupQ"
    DoCmd.OpenQuery "GroupTBackupQ"
    DoCmd.OpenQuery "SettingTBackupQ"
    DoCmd.OpenQuery "InventoryTBackupQ"
    DoCmd.OpenQuery "InventoryXOperationBackupQ"
    DoCmd.OpenQuery "ConsortiumTBackupQ"
    DoCmd.OpenQuery "DonorSignatureTBackupQ"
    DoCmd.OpenQuery "CollectorSignatureTBackupQ"
    DoCmd.OpenQuery "SignInTBackupQ"
    DoCmd.OpenQuery "DistrictTBackupQ"
    DoCmd.SetWarnings True
    
    FSO.CopyFile FilePath, FileDest, True
    MsgBox "Backup Successful!"
    
    Set FSO = Nothing


    End Sub

[CJ_London]

which line is causing the error? presumably the filecopy line but please be clear

Further, this line
FolderName = Format(Date, "mmmm") & Format(Date, "yy")

could just be

FolderName = Format(Date, "mmmmyy")

but does not result in a format of 'May22'. You would need to use

FolderName = Format(Date, "mmmmdd")



And how do you know all your make table queries have completed being made before trying to copy

And shouldn't


FileDest = "C:\Users\bogle\OneDrive - aavcor.com\Documents\DB Backups" & FolderName

be


FileDest = "C:\Users\bogle\OneDrive - aavcor.com\Documents\DB Backups" & FolderName & ".accdb"

(would think you need a file extension)

[moke123]

NM, deleted.

[Glenley]

Yes, Ajax, it's stopping on the file copy line and according to the locals window, it does format as "May22" but I'll try the other way. Should I need the file extension for a folder?

[Glenley]

Moke123, yes write permissions are turned on and yes the folder syncs with OneDrive but I have it set to Always Available on this PC

[Glenley]

And, yes the Make Table Queries are completing. The backup is actually running. It doesn't throw an error until it tries to copy the file

[Glenley]

I figured it out. I was copying to a folder and it was confusing the program since it was a CopyFile command. I did this

Private Sub btnBackupTables_Click()


    Dim FSO As New FileSystemObject
    Dim FilePath As String, FileDest As String
    Dim FolderName As String
    
    FolderName = Format(Date, "mmmmyy")
    FilePath = "C:\Users\bogle\OneDrive - aavcor.com\Documents\DB Backups\BackupsFromQuery_aavdb.accdb"
    FileDest = "C:\Users\bogle\OneDrive - aavcor.com\Documents\DB Backups\" & FolderName & "\" & "be_backup_" & Format(Date, "mm-dd-yyyy") & ".accdb"
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "CustomerTBackupQ"
    DoCmd.OpenQuery "DonorTBackupQ"
    DoCmd.OpenQuery "EmployeeTBackupQ"
    DoCmd.OpenQuery "LabTBackupQ"
    DoCmd.OpenQuery "MROTBackupQ"
    DoCmd.OpenQuery "OfficerEmployerTBackupQ"
    DoCmd.OpenQuery "ReasonForTestTBackupQ"
    DoCmd.OpenQuery "TestTBackupQ"
    DoCmd.OpenQuery "GroupTBackupQ"
    DoCmd.OpenQuery "SettingTBackupQ"
    DoCmd.OpenQuery "InventoryTBackupQ"
    DoCmd.OpenQuery "InventoryXOperationBackupQ"
    DoCmd.OpenQuery "ConsortiumTBackupQ"
    DoCmd.OpenQuery "DonorSignatureTBackupQ"
    DoCmd.OpenQuery "CollectorSignatureTBackupQ"
    DoCmd.OpenQuery "SignInTBackupQ"
    DoCmd.OpenQuery "DistrictTBackupQ"
    DoCmd.SetWarnings True
    
    FSO.CopyFile FilePath, FileDest, False
    MsgBox "Backup Successful!"
    
    Set FSO = Nothing


End Sub

[ssanfu]

Instead of DoCmd.OpenQuery, I would use CurrentdB.Execute
See Database.Execute method

 '   DoCmd.SetWarnings False
    CurrentdB.Execute "CustomerTBackupQ", dbFailOnError
    CurrentdB.Execute "DonorTBackupQ", dbFailOnError
    CurrentdB.Execute "EmployeeTBackupQ", dbFailOnError
    CurrentdB.Execute "LabTBackupQ", dbFailOnError
    CurrentdB.Execute "MROTBackupQ", dbFailOnError
    CurrentdB.Execute "OfficerEmployerTBackupQ", dbFailOnError
    CurrentdB.Execute "ReasonForTestTBackupQ", dbFailOnError
    CurrentdB.Execute "TestTBackupQ", dbFailOnError
    CurrentdB.Execute "GroupTBackupQ", dbFailOnError
    CurrentdB.Execute "SettingTBackupQ", dbFailOnError
    CurrentdB.Execute "InventoryTBackupQ", dbFailOnError
    CurrentdB.Execute "InventoryXOperationBackupQ", dbFailOnError
    CurrentdB.Execute "ConsortiumTBackupQ", dbFailOnError
    CurrentdB.Execute "DonorSignatureTBackupQ", dbFailOnError
    CurrentdB.Execute "CollectorSignatureTBackupQ", dbFailOnError
    CurrentdB.Execute "SignInTBackupQ", dbFailOnError
    CurrentdB.Execute "DistrictTBackupQ", dbFailOnError
 '   DoCmd.SetWarnings True

OR even better

 '   DoCmd.SetWarnings False
With CurrentdB
       .Execute "CustomerTBackupQ", dbFailOnError
       .Execute "DonorTBackupQ", dbFailOnError
       .Execute "EmployeeTBackupQ", dbFailOnError
       .Execute "LabTBackupQ", dbFailOnError
       .Execute "MROTBackupQ", dbFailOnError
       .Execute "OfficerEmployerTBackupQ", dbFailOnError
       .Execute "ReasonForTestTBackupQ", dbFailOnError
       .Execute "TestTBackupQ", dbFailOnError
       .Execute "GroupTBackupQ", dbFailOnError
       .Execute "SettingTBackupQ", dbFailOnError
       .Execute "InventoryTBackupQ", dbFailOnError
       .Execute "InventoryXOperationBackupQ", dbFailOnError
       .Execute "ConsortiumTBackupQ", dbFailOnError
       .Execute "DonorSignatureTBackupQ", dbFailOnError
       .Execute "CollectorSignatureTBackupQ", dbFailOnError
       .Execute "SignInTBackupQ", dbFailOnError
       .Execute "DistrictTBackupQ", dbFailOnError
End With
 '   DoCmd.SetWarnings True

[CJ_London]

it does format as "May22"

Ah - sorry, of course it is 2022, I had it in my head that as this is a nightly routine so thinking 22nd May otherwise you will be overwriting the previous backup until the next month.

Usually better to keep a number of current weeks backups in the event that you need to revert to one that is not the latest

[Glenley]

Ajax, this is actually a nightly routine. I just keep them in folders separated by month. I do appreciate the help because you did actually point me in the right direction when you suggested the '.accdb'

[Glenley]

ssanfu, interesting. I wasn't aware of this method. I understand if I run it the way you have it written, if an error occurs it will roll back any changes to the external DB file?

[moke123]

Usually better to keep a number of current weeks backups in the event that you need to revert to one that is not the latest

I use fso to iterate through my backup folder and look at the date created property. If the most recent is a week old it creates a new backup file. Conversely if the backup is over a month old it is deleted.

I had it in my head that as this is a nightly routine

It is run onload of an unbound login form so I suppose you could call it a "Daily" routine. Always have 4 backups. No user intervention needed.

[ssanfu]

@Glenley,
That is what I understand.
But the main reasons is that Jet is bypassed (which means execution is faster), you don't need to turn warnings off/on (which hides error messages) and if there is an error, you get a message.

if an error occurs it will roll back any changes to the external DB file?

but only for the query that has/had the error.