ODBC Connection

[j_Southern]

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

[j_Southern]

As there has been no immediate response to my ODBC link question, perhaps I can ask a more general one. I have used the connection string I found in the connection property window after I had successfully used MSquery . I used the SQL string I found in the SQL report in MSQuery. Should I have expected success when copying over to my VBA code, or are there some differences I should know about?
John

[j_Southern]

I have overcome the first problem by using a record set rather than a query table. I am still in difficulties though because I have some date variables which I need to put into the SQL string, and they are not working.
I am using the code shown me by xlnitwit which works with MSQuery but it seems not with a recordset.
This code works unless I add one or both lines marked ****

Public cnConnection As ADODB.Connection
Public strConnection As String
Public Sub Make_Connection()
   
     strConnection = "DSN=sqlb00-cerm;Description=sqlb00-cerm;UID=CermSys;Password=SysCerm01.;APP=Microsoft Office 2010;WSID=WTWS014;DATABASE=sqlb00;"
    Set cnConnection = New ADODB.Connection
    cnConnection.ConnectionString = strConnection
    cnConnection.Open
    
End Sub
Public Sub Close_Connection()
    cnConnection.Close
    Set cnConnection = Nothing
End Sub

Public Sub Load_Recordset(strSQL As String, SheetName)
    Dim rsRecordset As ADODB.Recordset
    
    Make_Connection
    
    
    Set rsRecordset = New ADODB.Recordset
    rsRecordset.ActiveConnection = cnConnection
    rsRecordset.CursorLocation = adUseClient
    rsRecordset.CursorType = adOpenDynamic
    
    rsRecordset.Open (strSQL)
    Sheets(SheetName).Range("A1").CopyFromRecordset rsRecordset
    rsRecordset.Close
    Set rsRecordset = Nothing
    
    Close_Connection
End Sub
Sub GetData(FromDate, ToDate)
Dim strSQL As String
   FDate = Format(CDate(FromDate), "yyyy/mm/dd")
   TDate = Format(CDate(ToDate), "yyyy/mm/dd")
    strSQL = BuildSQL(FDate, TDate)
     Load_Recordset strSQL, "DownLoad"

End Sub
Function BuildSQL(FDate, TDate)
strSQL = "SELECT bstlyn__.bst__dat, bstlyn__.dok__dat, bstlyn__.fac__tst, "
strSQL = strSQL & "bstlyn__.bedr__bm , afgprd__.omschr__, bstlyn__.bst__ref "
strSQL = strSQL & "FROM sqlb00.dbo.afgprd__ afgprd__, sqlb00.dbo.bstlyn__ bstlyn__, sqlb00.dbo.order___ order___ "
strSQL = strSQL & "WHERE order___.ord__ref = bstlyn__.ord__ref "
strSQL = strSQL & "AND order___.prd__ref = afgprd__.prd__ref "

'strSQL = strSQL & "AND (bstlyn__.dok__dat >{d'" & FDate & " '}) " ****
'And (bstlyn__.dok__dat <= {d' " & TDate & " '}))"                          ****

BuildSQL = strSQL
End Function

Can anyone show me where I have gone wrong
John

[j_Southern]

I have been looking at my problem from a different direction. I can manually prepare the query in MSQuery and use code to refresh it. This works but is clumsy as I have to have parameters for the dates.
However in the connections/properties window is a button marked parameters which lets you choose a cell to give its value, and this would be an excellent solution for me. When I try it however I get an error message telling me I have the wrong date format.
I have tried every format I can think of always with the same result. What format should I use?

Please someone help before I go stark staring bonkers!
John

[xlnitwit]

Hi,

I believe that for a querytable the connection string ought to begin with "ODBC;" which might explain the problems you had originally.

I would suggest you enter fixed dates in your SQL string rather than using parameters, in order to determine what format works. It should then be a fairly simple matter to convert to a parameter- it may simply be a question of entering a formatted text string, rather than an actual date, into the parameter cell.

[j_Southern]

xlnitwit

I have been entering fixed dates in the way you suggest and have found that MSQuery likes dates formatted yyyy/mm/dd. When I use a cell with such a formatted date I get an error.
I have not thought about putting in a string though so thanks for that I shall go away and try it.
John