I want to use code to connect Excel to an SQL database. I have successfully made this connection using MSQuery and have copied the SQL statement and the connection string to use in the code.
Const CERMCONN for the connection and
BuildSQL for the SQL String
 Sub DoDownLoad(FromDate, ToDate, SheetName)
FDate = Format(CDate(FromDate), "yyyy/mm/dd")
TDate = Format(CDate(ToDate), "yyyy/mm/dd")

       Const CERMCONN = "DSN=sqlb00-cerm;Description=sqlb00-cerm;UID=CermSys;PWD=SysCerm01.;APP=Microsoft Office 2010;WSID=WTWS014;DATABASE=sqlb00;"
                 Dim rngTape As Range
            Dim qtTAPEO As QueryTable
          Dim strSQL  As String

  strSQL = BuildSQL(FDate, TDate)
  
    Set rngTape = Sheets(SheetName).Range("A7")
      Set qtTAPEO = Sheets(SheetName).QueryTables.Add(CERMCONN, rngTape, strSQL)
         qtTAPEO.RefreshStyle = xlOverwriteCells
     qtTAPEO.Refresh False
  qtTAPEO.Delete

 End  Sub
 Function BuildSQL(FDate, TDate)
' SQLStr = "SELECT bstlyn__.bst__dat, bstlyn__.dok__dat, bstlyn__.fac__tst, "
' SQLStr = SQLStr & "bstlyn__.bedr__bm , afgprd__.omschr__, bstlyn__.bst__ref "
' SQLStr = SQLStr & "FROM sqlb00.dbo.afgprd__ afgprd__, sqlb00.dbo.bstlyn__ bstlyn__, sqlb00.dbo.order___ order___ "
' SQLStr = SQLStr & "WHERE order___.ord__ref = bstlyn__.ord__ref And order___.prd__ref = afgprd__.prd__ref "
' SQLStr = SQLStr & "AND ((bstlyn__.dok__dat Between {d' " & FDate & " '} And {d' " & FDate & " '})) "

  SQLStr = "SELECT bstlyn__.dok__dat "
   SQLStr = SQLStr & "FROM sqlb00.dbo.bstlyn__ bstlyn__"
 BuildSQL = SQLStr
 End Function
As you can see I have simplified the SQL String as far as I can.
I get an error at the line Set qtTAPEO = Sheets(SheetName).QueryTable.Add(CERMCONN, rngTape, strSQL)
My question is how should I proceed to find if it is the SQL string or the Connection line that is giving the error
John