vba 使用 Excel 宏 (ADODB) 在 SQL Server 2008 中创建临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14939766/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a temp table in SQL Server 2008 using an Excel Macro (ADODB)
提问by Kali_89
After lots of Googling, I've ended up with the following macro that I hoped would connect to a database, drop any existing temp table and then create a new one (populate it, and view the results).
经过大量的谷歌搜索后，我最终得到了以下宏，我希望它可以连接到数据库，删除任何现有的临时表，然后创建一个新表（填充它并查看结果）。
Dim adoCn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim adoCm As ADODB.Command
Dim strSQL As String
Set adoCn = New ADODB.Connection
With adoCn
.ConnectionString = "Provider=SQLOLEDB;" & _
"Initial_Catalog=XXX;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Data Source=XXX;" & _
"Extended Properties='IMEX=1'"
.CursorLocation = adUseServer
.Open
End With
Set adoCm = New ADODB.Command
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdText
.CommandText = "IF OBJECT_ID('tempdb..#AgedProducts') IS NOT NULL DROP TABLE #AgedProducts"
.Execute
.CommandText = "CREATE TABLE #AgedProducts " & _
"(Source_Order_Number VARCHAR(255)) " & _
"INSERT INTO #AgedProducts VALUES ('AB-123-456') " & _
"SELECT * FROM #AgedProducts (NOLOCK) "
.Execute
End With
Set adoRs = New ADODB.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockBatchOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm
MsgBox "Recordset returned...", vbOKOnly
While Not adoRs.EOF
Debug.Print adoRs.Fields(0).Value
adoRs.MoveNext
Wend
adoCn.Close
Set adoCn = Nothing
Set adoRs = Nothing
When I run the query I get the following error message:
当我运行查询时，我收到以下错误消息：
Run-time error '-2147217887 (80040e21)':
Run-time error '-2147217887 (80040e21)':
The requested properties cannot be supported
The requested properties cannot be supported
The NOCOUNTline comes from http://support.microsoft.com/kb/235340(as does much of the above code). I've added IMEX=1to take into account order number might have multiple types in there but I doubt that's where the problem is happening.
该NOCOUNT行来自http://support.microsoft.com/kb/235340（与上面的大部分代码一样）。我已经添加IMEX=1考虑到订单号可能有多种类型，但我怀疑这就是问题发生的地方。
Any help is greatly appreciated!
任何帮助是极大的赞赏！
采纳答案by dee
Modify the way how the recodset is opened, move the select from the command to the recodset open method call.
修改recodset的打开方式，将select从命令移到recodset open方法调用。
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdText
.CommandText = "IF OBJECT_ID('tempdb..#AgedProducts') IS NOT NULL DROP TABLE #AgedProducts"
.Execute
.CommandText = "CREATE TABLE #AgedProducts " & _
"(Source_Order_Number VARCHAR(255)) " & _
"INSERT INTO #AgedProducts VALUES ('AB-123-456') "
.Execute
End With
Set adoRs = New ADODB.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockBatchOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
End With
adoRs.Open "SELECT * FROM #AgedProducts (NOLOCK)"
回答by Dan Bracuk
My understanding of temp tables is that they are only available to the connection that created them. That being the case, attempting to drop one from another connection is unwise.
我对临时表的理解是它们只对创建它们的连接可用。在这种情况下，试图从另一个连接中删除一个是不明智的。
The error message does not state which line of code caused it. That being the case, I suggest that you test your code piecemeal. Start by creating the connection. Then open and close it. Then start doing things while the connection is open, but one thing at a time.
错误消息没有说明是哪一行代码导致的。既然如此，我建议您逐步测试您的代码。首先创建连接。然后打开和关闭它。然后在连接打开时开始做一些事情，但一次做一件事。
