VBA to SQL query with parameters

[krissz]

I am trying to write an SQL query using parameters in an Excel Worksheet and then return the results to the current sheet ar cell B11. I have found it imposible to make it work in SQL so am trying to run it from VBA to pass the parameters. These are named cells StartDate and EndDate in cells A2 & A3 respectively and should replace the constant dates below. I am working in Excel2003 & MS SQL 2012.

I have tested the query on SQL server with fixed values (which are still in it). It works fine & I have pasted it in.

I took an existing query I found & tried to modify it for Excel2003 but ... Whatever syntax I use, I get a Run-time error 424 when I get to a line with 'connection'

Sub DateQuery()

   With ActiveWorksheet.connections("ndserver01").oledbconnection
      
'      .Connection = "OLEDB; DSN=Excel Files; provider=SQLOLEDB; Catalog=NDM_Sage200;Data Source=ndserver01"
      .Refresh BackgroundQuery:=True
      .CommandType = xlCmdSql
      
'      .ActiveWorkbook.ActiveSheet.Select
'      .Range("a2:a3").Select
      
      .CommandText = "SELECT   DISTINCT A.AccountNumber, AccountName AS 'Name', COALESCE(R.SumAc,0) AS 'SumAc'" & _
      "FROM qryNLSLPLPostedTrans A LEFT JOIN" & _
      "(" & _
      "SELECT   AccountNumber, SUM(GoodsValueInBaseCurrency) AS 'SumAc'" & _
      "(" & _
      "SELECT   AccountNumber, SUM(GoodsValueInBaseCurrency) AS 'SumAc'" & _
      "FROM qryNLSLPLPostedTrans" & _
      "WHERE TransactionDate BETWEEN '2012-07-01' AND '2013-06-30'" & _
      "GROUP BY AccountNumber" & _
      ") R" & _
      "ON A.AccountNumber=R.AccountNumber" & _
      "ORDER BY AccountNumber, AccountName"
   
   
   End With
        
   ActiveWorkbook.connections("NDServer01").Refresh


End Sub

What have I done wrong

 

[krissz]

I have managed to get further. Having gone through various ideas garnered from t'internet, I have completely rewritten the code as below. When I get it working, the intention is to replace the 2 dates by cell references.

However, I now get an error on the 'Execute' line at the end:
Run-time error '-2147217900 (80040e14)' Automation error.

The code is as follows

Sub DateQuery()

'  Set the connection
   Const ConStr As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NDM_Sage200;Data Source=ndserver01"
   Dim Cnct1 As ADODB.Connection
   Set Cnct1 = New ADODB.Connection
   Cnct1.ConnectionString = ConStr
   Cnct1.Open
   
'  Set the SQL Command
   Dim SQLString As String
   SQLString = "SELECT   DISTINCT A.AccountNumber, AccountName AS 'Name', COALESCE(R.SumAc,0) AS 'SumAc'" & _
      "FROM qryNLSLPLPostedTrans A LEFT JOIN" & _
      "(" & _
      "SELECT   AccountNumber, SUM(GoodsValueInBaseCurrency) AS 'SumAc'" & _
      "(" & _
      "SELECT   AccountNumber, SUM(GoodsValueInBaseCurrency) AS 'SumAc'" & _
      "FROM qryNLSLPLPostedTrans" & _
      "WHERE TransactionDate BETWEEN '2012-07-01' AND '2013-06-30'" & _
      "GROUP BY AccountNumber" & _
      ") R" & _
      "ON A.AccountNumber=R.AccountNumber" & _
      "ORDER BY AccountNumber, AccountName"
   
'  Set the Command
   Dim SQLCmd As ADODB.Command
   Set SQLCmd = New ADODB.Command
   SQLCmd.CommandText = SQLString
   SQLCmd.CommandType = adCmdText
   Set SQLCmd.ActiveConnection = Cnct1
    
'  execute query
   SQLCmd.Execute
   
           
   ActiveWorkbook.Connections("NDServer01").Refresh


End Sub

Any help would be much appreciated