Excel VBA withSQL Variables

Question

Hi,

Excel 2007
SQL Server 2005.

I'm new to all of this, so please bear with me...I've searched high and low for an answer, but cannot find anything on this.

I have an excel sheet which and populated by a user that then provides the inputs for an SQL query.

I want to take these inputs and then pass an (fairly long) SQL query over to SQL Server and then read the results within excel.

Note: I only have read access to the database, and so cannot create stored procedures.

Here's the query that I've constructed in Excel as a String variable, named queryStr

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

DECLARE @results TABLE(rate DECIMAL(18, 9));
DECLARE @rate DECIMAL(18,9);

 SELECT * FROM @results;

SET @rate = (SELECT rate FROM ValuSpreadCdsRate WHERE cdsDataId = 123456 AND effectiveDate = '01/01/2000'); 
--cdsDataId and effectiveDate are sourced from Excel

INSERT INTO @results VALUES (@rate);

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

I am then using a RecordSet object (called "rs") to get the results, as follows:

rs.Open queryStr 
varA = = rs.Fields("rate").Value   'this line this throws the error below

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

When I copy and paste the query in SQL Server Management Studio, I get my result without any errors.

When I add the "rs" to my watch window, the object appears to be closed, as many of the properties state "<Operation is not allowed when the object is closed>"

Note that if I substitute the sql above with a straightforward query (i.e. one that doesn't use any inputs from the excel sheet), I am able to get results - so there is no problem with the recordset opening.

I've run some tests, and from what I can see the problem is the statement "SET @rate".

So if this is indeed the problem, my question is:
when declaring SQL variables within VBA, how do get results?

Some possible solutions may be:
Don't use a recordset object
Don't use a string to create this query
Don't use ADODB (which is something I am not very familiar with).

As I said, I'm pretty new to this, so please go easy with the jargon!

Thanks

Answer 1

Hello,

I've tested your code and could confirm that in VBA you could use ADODB.Connection.Execute method to run above sql querys, but I was failed above query just beacuse I don't have the same database structure as yours.

Here's the code I've ：

Sub connect()
'Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=pubs;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn
Dim cmd As String
 cmd = "DECLARE @results TABLE(rate DECIMAL(18, 9));DECLARE @rate DECIMAL(18,9);" & _
"SELECT * FROM @results;" & _
"SET @rate = (SELECT * FROM Jobs );" & _
"INSERT INTO @results VALUES (@rate);"

 

cnPubs.Execute cmd
End Sub

I could verify above works through the "SQL Profiler", may be you can use the same way to check your query in SQL side. For the exceptions in VBA side, go to "Debug" menu in your VBE, and "Add Watch" to your Recordset object and set a break when your code hits the line, you'll be able to check variable in "Watches" window.

Thanks.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Maybe you can get some help from this post: http://support.softartisans.com/kbview_231.aspx .

---

You can find almost all the answers to your questions with the help of forum.