Problem with 1004 error on refresh data connection

[Sixxthson]

Hi,

I'm getting a runtime 1004 error: application or object defined error on the code below

Seems to be in relation to

"ActiveWorkbook.Connections("Database2").Refresh,"

Any advice appreciated.

Sub Send2Access2()
Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim dbCommand As New ADODB.Command
                'Define 
    
                'Establish the connection then locate the recordset
    cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=\\W2K6082\common\shared\ShiftSwap\Database2.accdb; Persist Security Info = False;"
    rst.Open "Shift_Swap", cnn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    dbCommand.ActiveConnection = cnn
                'Execute the connection command
    
    
    With rst
                'with recordset
        .AddNew
                'new fields
        .Fields("Date Submitted") = Worksheets("Submission").Range("A50").Value
        .Fields("Agent Email") = Worksheets("Submission").Range("E11").Value
        .Fields("Date Requested") = Worksheets("Submission").Range("E12").Value
        .Fields("Payback Date 1") = Worksheets("Submission").Range("E13").Value
        .Fields("Payback Date 2") = Worksheets("Submission").Range("E14").Value
        .Fields("Shift Start") = Worksheets("Submission").Range("E15").Value
        .Fields("Shift end") = Worksheets("Submission").Range("E16")
        .Fields("RDO") = Worksheets("Submission").Range("E17").Value
        .Fields("Call Type") = Worksheets("Submission").Range("E18").Value
                'if needed more fields here
        .Update
                 
   End With
    
ActiveWorkbook.Connections("Database2").Refresh
 
    rst.Close
    Set rst = Nothing
                'close the recordset
    cnn.Close
    Set cnn = Nothing
                'Close the connection
End Sub

 

[CharlesChuckieCharles]

should

"ActiveWorkbook.Connections("Database2").Refresh,"

actually Be

"ActiveWorkbook.Connections("Shift_Swap").Refresh,"

??

 

[Sixxthson]

should

"ActiveWorkbook.Connections("Database2").Refresh,"

actually Be

"ActiveWorkbook.Connections("Shift_Swap").Refresh,"

??

Tried the above and it comes back with a runtime 9 error saying : subscript out of range.

Any ideas?

 

[CharlesChuckieCharles]

Looking at it more closely do you need to do this?

ActiveWorkbook.Connections("Database2").Refresh


this
"ActiveWorkbook.Connections("Shift_Swap").Refresh,"

total erroneous since Shift_Swap is obviously the Record Set
so apologies