Getting error 1004 when calling refresh from within VBA

[msaxton]

I am getting an error 1004 when calling refresh of a query within VBA. If I refresh the query manually, it works. The VBA code reads a table and determines the highest notes sequence currently used. It then reads a list of notes to add to the table and one by one, updates the insert query and calls the refresh.

The strange things is that even when I get the error, the database shows the first notes records being added. Why would to work but then raise an error during the refresh?

What's even more confusing is I added On Error Resume Next right before the offending refresh and the code works just fine.

Anyone have any suggestions why this would be happening?


Below is the full code of the sub:

Sub UpdateSyteline()

Dim wbSourceWB As Workbook
Dim shDataSheet As Worksheet
Dim shCurNotesSheet As Worksheet


Dim lCurKey As Long
Dim lCurSeq As Long
Dim sSerialNumber As String


Dim lStartRow As Long
Dim lEndRow As Long
Dim lNumSerials As Long
Dim lFromRow As Long

Dim lFromCol As Long



Set wbSourceWB = ActiveWorkbook

Set shDataSheet = Worksheets("Data")
Set shCurNotesSheet = Worksheets("Current Notes")

' Refresh query to see current notes
wbSourceWB.Connections("CurrentJobNotes").Refresh

' Get Key and highest sequence for current notes
lCurKey = shCurNotesSheet.Range("C2").Value
lCurSeq = shCurNotesSheet.Range("E2").Value

' figure out how many Serial lines we have
shDataSheet.Range("C8").Select
lStartRow = ActiveCell.Row
If shDataSheet.Range("C9").Value = "" Then
lEndRow = lStartRow
Else
Selection.End(xlDown).Select
lEndRow = ActiveCell.Row
End If
lNumSerials = lEndRow - lStartRow + 1

' For each serial in master sheet
For lFromRow = lStartRow To lEndRow

' Get serial Number
sSerialNumber = shDataSheet.Cells(lFromRow, 2)
If sSerialNumber = "" Then sSerialNumber = SerialToJulian(Date)

' Add 1 to current sequence
lCurSeq = lCurSeq + 1

' Update insert SQL command
wbSourceWB.Connections("InsertJobNotes").ODBCConnection.CommandText = "Insert into vmain.notes (key, seq, new-paragraph, txt) " _
& " values (" & lCurKey & ", " & lCurSeq & ", True, '" & sSerialNumber & "')"
On Error Resume Next
'Refresh insert query to write to database
wbSourceWB.Connections("InsertJobNotes").Refresh
On Error GoTo 0
Next lFromRow

End Sub 'UpdateSyteline