adodb recordsets and command text

[RAYLWARD102]

I'm very familiar with fetching a query based record set, via Excel VBA adodb connection to an access or SQL table.
Recently, I discovered a whole other level of SQL syntax, allowing one to declare variables and do interesting things.
I wanted to know how I can execute statements that include SQL declares into my record set and can't seem to figure it out. My statements definitely work, since I tehy execute flawlessly in my SQL Visual studio debugger;
The vba screenshot, works if I do a simple query like "SELECT * FROM hrdat.dbo.absence" with no issues, so the connection is open and ready; but seems the query needs to be delivered differently possibly when using declare and other sql syntax; any ideas would be appreciated.

 

[RAYLWARD102]

after looking around, discovered adding SET NOCOUNT ON; at the beginning of my sql statement, somehow solves the problem. Not sure why, but that's the answer I found.

 

[smozgur]

In the Messages tab in the SQL query editor, you should be seeing the record count returned by the SELECT statement (or affected record count for action queries). It is the first recordset that your query returns. And you make it silent by using the SET NOCOUNT ON statement as you already figured. So, it looks like VBA gets the first recordset result, and halts since it is not what it is expecting as the desired recordset.

Glad to hear you got the answer, and thanks for posting back for future readers.