SQL - ODBC - VBA in Excel - Run-time error '1004'

Hi,

I built a code that executes an SQL code over an ODCB connection.  My problem is that this code is working well on my computer but isn't working on 6 others similar computer that I tried my code.

Error message : Run-Time Error '1004'; Application-defined or object-defined error.  This appear at the last line of the code: ThisQt.Refresh.

Here is my code :
Sub Import()
Dim thisSheet As Worksheet
Dim sql As String
Dim sqlWhere As String
Dim DB_NAME As String
Dim connString As String
Dim thisQT As QueryTable
Dim StartDate As String
Dim EndDate As String

Sheet1.Range("A3:AV65536").Clear

Set thisSheet = Sheet1

StartDate = Year(Sheet2.Range("B5")) & Format(Month(Sheet2.Range("B5")), "00") & Format(Day(Sheet2.Range("B5")), "00")
EndDate = Year(Sheet2.Range("b6")) & Format(Month(Sheet2.Range("b6")), "00") & Format(Day(Sheet2.Range("b6")), "00")

sql = "SELECT * FROM CANADA.KB4400CSTM.FCMSFC54C FCMSFC54C"

If Sheet2.Range("B3") = "" Then
    If Sheet2.Range("B4") = "" Then
        sqlWhere = " WHERE (FCMSFC54C.L5DTTS between " & StartDate & " And " & EndDate & ")"
        Else
        sqlWhere = " WHERE (FCMSFC54C.L5CO=" & Sheet2.Range("B4") & ") AND (FCMSFC54C.L5DTTS between " & StartDate & " And " & EndDate & ")"
    End If
    Else
    If Sheet2.Range("B4") = "" Then
        sqlWhere = " WHERE (FCMSFC54C.L5DPTG='" & Sheet2.Range("B3") & "') AND (FCMSFC54C.L5DTTS between " & StartDate & " And " & EndDate & ")"
        Else
        sqlWhere = " WHERE ((FCMSFC54C.L5CO=" & Sheet2.Range("B4") & ") AND (FCMSFC54C.L5DPTG='" & Sheet2.Range("B3") & "') AND (FCMSFC54C.L5DTTS between " & StartDate & " And " & EndDate & "))"
    End If
End If

DB_NAME = "AS400"

connString = "ODBC;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=CANADA;DBQ=AS400"

Set thisQT = thisSheet.QueryTables.Add(connString, Sheet1.Range("A3"), sql & sqlWhere)

thisQT.BackgroundQuery = False
thisQT.Refresh

End Sub

Many thanks

MarcStart Free Trial