Problem with Selection.QueryTable.Refresh BackgroundQuery:=False

[BryanS]

I inherited an excel spreadsheet from a programmer who is now long gone. It uses a macro and ODBC to access some data on the AS/400. We recently merged 2 partitions on the AS/400 and the macro stopped working. I updated the ODBC configuration to point to the correct partition, but then the macro throws an error at the line

Selection.QueryTable.Refresh BackgroundQuery:=False

I get a Run-time error '1004' General ODBC error.

When I comment out the 'Selection.QueryTable' line the macro runs but the user says it returns no data.

I am flummoxed. Any help would be appreciated.

Bryan

[Tarball]

Add this to the code below your connection parameters. It will print out the exact ODBC error codes encountered

With Worksheets(1).QueryTables(1)
    '.Refresh
    Set errs = Application.ODBCErrors
    If errs.Count > 0 Then
        Set r = .Destination.Cells(1)
        r.Value = "The following errors occurred:"
        c = 0
        For Each er In errs
            c = c + 1
            r.Offset(c, 0).Value = er.ErrorString
            r.Offset(c, 1).Value = er.SqlState
        Next
    Else
        MsgBox "Query complete: all records returned."
    End If
End With