Weakday, shell backup

[Hello1]

Im trying to make a backup of the current database on a button click. The code should copy the current database to a flashdrive and make a folder with the current day name and put the database in it.

The following code works fine.

Private Sub cmdBackup_Click()
Dim aaa
On Error GoTo Err_cmdBackup_Click
    Dim frm As Form
        Set MyDb = CurrentDb
        While Forms.Count > 1
                For Each frm In Forms
                        If IsLoaded(frm.Name) And frm.Name <> Me.Name Then
                                DoCmd.Close acForm, frm.Name
                        End If
                Next frm
        Wend

Select Case Weekday(Date)
        Case 1  'Sunday
            aaa = Shell("xcopy  E:\DB\database.mdb  Z:\1_Sunday\ /Y", vbHide)
        Case 2  'Monday
            aaa = Shell("xcopy  E:\DB\database.mdb  Z:\2_Monday\ /Y", vbHide)
        Case 3  'Tuesday
            aaa = Shell("xcopy  E:\DB\database.mdb  Z:\3_Tuesday\ /Y", vbHide)
        Case 4  'Wednesday
            aaa = Shell("xcopy  E:\DB\database.mdb  Z:\4_Wednesday\ /Y", vbHide)
        Case 5  'Thursday
            aaa = Shell("xcopy  E:\DB\database.mdb  Z:\5_Thursday\ /Y", vbHide)
        Case 6  'Friday
            aaa = Shell("xcopy  E:\DB\database.mdb  Z:\6_Friday\ /Y", vbHide)
        Case 7  'Saturday
            aaa = Shell("xcopy  E:\DB\database.mdb  Z:\7_Saturday\ /Y", vbHide)
    End Select
        MsgBox "Backup done", vbInformation, "Database 1.0"

Exit_cmdBackup_Click:
    Exit Sub

Err_cmdBackup_Click:
    MsgBox Error$, vbCritical, "Database 1.0"
    Resume Exit_cmdBackup_Click
End Sub

However, I want instead the path to use a variable which will get the path from a table.
In the table I have 2 fields, one DBPath and the other one USBPath, so I tried next code (I will include only 1 day to not spam too much).
The DBPath field has this value E:\DB\database.mdb
The USBPath this one Z:\

Private Sub cmdBackup_Click()
Dim DBPath As String
Dim USBPath As String
Set MyDb = CurrentDb
Set MyRS = MyDb.OpenRecordset("SELECT DBPath, USBPath, FROM tblPath)
    DBPath = MyRS!DBPath
    USBPath = MyRS!USBPath

Dim aaa
On Error GoTo Err_cmdBackup_Click
    Dim frm As Form
        Set MyDb = CurrentDb
        While Forms.Count > 1
                For Each frm In Forms
                        If IsLoaded(frm.Name) And frm.Name <> Me.Name Then
                                DoCmd.Close acForm, frm.Name
                        End If
                Next frm
        Wend

Select Case Weekday(Date)
	Case 5  'Thursday
            aaa = Shell("xcopy " & DBPath & "  " & USBPath & "5_Thursday\ /Y", vbHide)
End Select
        MsgBox "Backup done", vbInformation, "Database 1.0"

Exit_cmdBackup_Click:
    Exit Sub

Err_cmdBackup_Click:
    MsgBox Error$, vbCritical, "Database 1.0"
    Resume Exit_cmdBackup_Click
End Sub

The both variables do get the correct values but xcopy part and the new day folder creation and the actual backup doesnt work.

Thanks, hope I have been clear enuff

 

[theDBguy]

Hi. Just for troubleshooting purposes, you could try storing the command in a variable and then examine the content of the variable to make sure it is what you think it should be.

 

[Hello1]

Turns out I misspelled the location name of the .mdb when I wrote it in the table, jeez
Need to learn to start from the simplest mistakes and not immediately dig deep :banghead:

Thanks for the reply