Code in database to back up, not working

[Diana van den Berg]

I have Windows 7 Professional and Microsoft Access 2010.

I have a Back End of a database called ABC_Membership_be.accdb.

Its path is Documents\TCS\ABC.

I have a database to back up ABC_Membership_be.accdb into Documents\TCS\ABC\BACKUPS.

However, I am getting a Path not found error message when I run it.

Please will somebody help me correct the code below:

Code: Select all

Private Sub CreateBackup_Click()
Dim strFolder As String
Dim strBE As String
Dim strBackup As String

    strFolder = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & _
                "\TCS" & _
                "\ABC\"

    strBE = "ABC_Membership_be.accdb"
    strBackup = " ABC _Membership_be_BU_" & Format(Now, "yyyy-mm-dd""_at_""hh""h""nn") & ".accdb"
    On Error GoTo ErrHandler
    FileCopy strFolder & strBE, strFolder & "TCS\ABC\BACKUPS\" & strBackup
    MsgBox "Backup created!", vbInformation
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
End Sub

Thank you for any help.

[Rudi]

Try this version:

Code: Select all

Private Sub CreateBackup_Click()
Dim strFolder As String
Dim strBE As String
Dim strBackup As String

    strFolder = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & _
                "\TCS" & _
                "\ABC\"

    strBE = "Test.xlsx"
    strBackup = "Test_BU_" & Format(Now, "yyyy-mm-dd""_at_""hh""h""nn") & ".xlsx"
    On Error GoTo ErrHandler
    FileCopy strFolder & strBE, strFolder & "BACKUPS\" & strBackup
    MsgBox "Backup created!", vbInformation
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
End Sub

You specify the folder path including the "\TCS\ABC\" in the strFolder declaration.
Then the line that copies the backup points to "\TCS\ABC\BACKUPS", effectively making it "\TCS\ABC\TCS\ABC\BACKUPS".

[Diana van den Berg]

You are a star, Rudi! Thank you! That was perfectimus!