Executing SQL Stored Procedure through Excel (VBA) with multiple parameters

Question

Thanks for your help, @FreeMan.

However, I am still lost with this.

Let me just start from scratch.

This is my query in summary ( I hope it is clear)

SELECT DISTINCT Q1,Q2,Q3

from(SELECT A,B,C) Q1

full outer join

(SELECT A,B,C

from tblA as a

join tblB as b

on a.aID = b.bID

join tblC as c

on b.bID = c.cID

where (a,b, cdate <= ?

group by c) Q2

ON Q1.A = Q2.B

WHERE Convert(DATETIME, Q1.[B], 103) <= ?

order by Q1.[A]

When I try to refresh my table, I get the following error message

"[Microsoft][ODBC SQL Server Driver]Invalid Parameter number" and then

"[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index"

I need a method that will allow me to run this query using the parameters in a specified cell i.e. cell C3.

I hope this makes things a bit clearer I am not familiar with VBA so all the commands advised above are confusing to me.

Original Question

*[I am a newbie when it comes to VBA and so I am struggling to get my query to run with more than one parameter. I am using this macro as but when I run it it tells me the second parameter has not been provided.

Sub RefreshQuery()

With ActiveWorkbook.Connections("MYSERVER").OLEDBConnection
    .CommandText = "EXECUTE dbo.Tng_Market_Feed '" & Range("B2").Value & "'"
End With
ActiveWorkbook.Connections("MYSERVER").Refresh

End Sub

My questions are:- 1. How do I set the variables to date formats? I need a report to show me all transactions <= a certain date (variable parameter) 2. The location of the variable in the spreadsheet, cell b2, remains the same for both parameters. For example, I need a list of all transactions <= 31/12/2014 and a value/sum of bad debt provision as at the same date 31/12/2014.]*

Answer 1

Here's a quick sample of creating and setting parameters from within your code:

Sub CreateParam()

     Dim oQuery As QueryTable
     Dim oParam As Parameter

     Set oQuery = Sheet3.QueryTables(1)

     oQuery.CommandText = Replace(oQuery.CommandText, "='Berlin'", "=?")

     Set oParam = oQuery.Parameters.Add("CityParam")

     oParam.SetParam xlRange, Sheet3.Range("J1")
     oParam.RefreshOnChange = True

     oQuery.Refresh

End Sub

^{code quickly cribbed from dicks-clicks.com}

Basically you'll need to execute the .Parameter.Add() line for each parameter in your stored procedure, and you'll be good to go. Note that you don't need to write a whole Sub() to do it, you can just include the Set oParam = oQuery.Parameters.Add() line in your existing code.

You may want to dig into the the options a bit, because you can specify the type of data being sent, and can also set up an OUT parameter, as well, to accept data back from the query.