Excel VBA - SQL调用 - 当对象关闭时不允许操作

Question

我有几个宏调用SSMS 2014来运行查询，并将结果返回到工作表中定义的单元格中。他们成功地工作，但是当我尝试使用与临时表的某些查询我收到以下错误信息：

我已经在网上进行搜索，最好的答案，我能找到的是在开始时添加SET NOCOUNT ON我的查询。我试过了，仍然有相同的信息。

的一段代码，该Debug使我想到如下：

bqr.Range("B6").CopyFromRecordset rst

我的代码的香饽饽，与重要的变量设置一起，如下：

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 

最容易混淆的部分是，错误表明我的rst记录集已关闭，即使它在我使用CopyFromRecordset

之前打开

我试过在我的查询结尾添加DROP TABLE，SET NOCOUNT ON函数开头，甚至测试了一些较小的简单临时表作为测试。

例如，我在我的QUERY变量设置为：

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" 

然后跑代码提取和过去到Excel，和它的工作。

因此，我很难过。查询可能会有多长时间？现在它是180线长，所以它是相当大的...

任何建议表示赞赏！

编辑：低于满宏（实际查询以下）：

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

与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" 

启动T-SQL查询那么它应该工作。下一个例子也可以工作，并且更接近你的例子（但使用表变量）。

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