Excel VBA - SQL Call - Operation is not allowed when the object is closed

Question

I have a couple macros to make calls to SSMS 2014 to run a query and return the results in a defined cell in my worksheet. They work successfully, but when I try to use certain queries with temp tables I get the following error message:

I have researched online and the best answer I can find is to add SET NOCOUNT ON at the beginning of my query. I tried that, and still got the same message.

The piece of code that the Debug brings me to is as follows:

bqr.Range("B6").CopyFromRecordset rst

The meat and potatoes of my code, along with the variable setups that matter, is as follows:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Dim SOURCE As String
Dim DATABASE As String
Dim QUERY As String
Dim intColIndex As Integer
Dim sDate As String
Dim eDate As String
Dim qt As Worksheet
Dim qtr As Worksheet
Dim bqr As Worksheet
Dim bp As Worksheet

ConnectionString = "Provider=SQLOLEDB;Data Source=" & SOURCE & "; Initial Catalog=" & DATABASE & "; Integrated Security=SSPI;"
cnn.Open ConnectionString

cnn.CommandTimeout = 900

StrQuery = QUERY

rst.Open StrQuery, cnn

bqr.Range("B6").CopyFromRecordset rst

For intColIndex = 0 To rst.Fields.Count - 1
    Range("B5").Offset(0, intColIndex).Value = rst.Fields(intColIndex).Name
Next

The most confusing part is that the error suggests that my rst recordset is closed, even though it is opened just before I use the CopyFromRecordset

I've tried adding DROP TABLE at the end of my query, the SET NOCOUNT ON function at the beginning, and even tested some smaller simple temp tables as tests.

For example, I set my QUERY variable to:

QUERY = "CREATE TABLE #Test1 (TestID INT, TestValue VARCHAR(20))"
QUERY = QUERY + " INSERT INTO #Test1"
QUERY = QUERY + " VALUES (1, 'Pass'), (2, 'Fail'), (3, 'Try Again')"
QUERY = QUERY + " SELECT * INTO #Test2 FROM #Test1 WHERE TestID = 1"
QUERY = QUERY + " SELECT * FROM #Test2"

Then ran the code to extract and past into Excel, and it worked.

Therefore, I am stumped. Maybe there is a limit to how long the query can be? Right now it's 180 lines long, so it's pretty big...

Any suggestions are appreciated!

EDIT: Full macro below (less the actual query):

Private Sub CommandButton1_Click()

If TextBox1.Value = "i.e. 20160101" Or TextBox2.Value = "i.e. 20160131" Then

MsgBox "Please fill out all fields before proceeding"

ElseIf Len(TextBox1.Value) <> 8 Or Len(TextBox2.Value) <> 8 Or Not IsNumeric(TextBox1.Value) Or Not IsNumeric(TextBox2.Value) Then

MsgBox "Please use correctly formatted Datekeys (i.e. yyyymmdd)"

Else

Application.DisplayAlerts = False

Sheets(ActiveWorkbook.Sheets.Count).Select

While ActiveSheet.Name <> "[worksheet I want to keep]"

ActiveSheet.Delete

Sheets(ActiveWorkbook.Sheets.Count).Select

Wend

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Dim SOURCE As String
Dim DATABASE As String
Dim QUERY As String
Dim intColIndex As Integer
Dim sDate As String
Dim eDate As String
Dim qtr As Worksheet
Dim bqr As Worksheet
Dim bp As Worksheet

Set qtr = Sheets([sheet name])

Sheets.Add after:=qtr
Set bqr = ActiveSheet
bqr.Name = "[sheet name]"
Sheets.Add after:=bqr
Set bp = ActiveSheet
bp.Name = "[sheet name]"

SOURCE = "[server]"
DATABASE = "[database]"
sDate = UserForm1.TextBox1.Value
eDate = UserForm1.TextBox2.Value

QUERY = "[beginning of query]"
QUERY = QUERY + " [more query here]" 'This gets repeated a lot for each additional line in the query'

qtr.Select
Range("B6").Select

While ActiveCell.Value <> ""

QUERY = QUERY + " " + ActiveCell.Value

ActiveCell.Offset(1, 0).Select

Wend

QUERY = QUERY + " [more query here]" 'This gets repeated a lot for the remaining lines in the query'



    ConnectionString = "Provider=SQLOLEDB;Data Source=" & SOURCE & "; Initial Catalog=" & DATABASE & "; Integrated Security=SSPI;"

    cnn.Open ConnectionString

    cnn.CommandTimeout = 2000


    StrQuery = QUERY


    rst.Open StrQuery, cnn

    bqr.Range("B6").CopyFromRecordset rst
For intColIndex = 0 To rst.Fields.Count - 1
    Range("B5").Offset(0, intColIndex).Value = rst.Fields(intColIndex).Name
Next

End If

Application.DisplayAlerts = True

End Sub

Answer 1

Start your T-SQL query with set nocount on;

QUERY = "set nocount on;"
QUERY = QUERY & "declare @Test1 table (TestID INT, TestValue VARCHAR(20))"
QUERY = QUERY & " INSERT INTO @Test1"
QUERY = QUERY & " VALUES (1, 'Pass'), (2, 'Fail'), (3, 'Try Again')"
QUERY = QUERY & " SELECT * FROM @Test1 WHERE TestID = 1"

Then it should work. The next example will also work and is a bit closer to your example (yet using table variables).

set nocount on;
declare @Test1 table (TestID INT, TestValue VARCHAR(20))
declare @Test2 table (TestID INT, TestValue VARCHAR(20))

INSERT INTO @Test1
VALUES (1, 'Pass'), (2, 'Fail'), (3, 'Try Again')

insert into @Test2
select *
from @Test1 WHERE TestID = 1

select * from @Test2