需要帮助使用 VBA 中的 ADO 创建临时表

Need help creating temporary table using ADO from VBA

提问by CBRF23

I've searched pretty extensively and found a lot of topics on this which helped me get my code to the point it is, but I'm at an impasse as I'm not sure what I'm doing wrong here. I'm trying to create a temporary table so that I can pull info from a couple of other tables, drop it in, then push it out to excel. Right now I was just trying to create a recordset from the temporary table to check that it is being created, and I'm getting an error on the select * from temporary table line.
The error I'm getting is: error -2147217865 (800040e37) Invalid object name '#TempGetBOM'

我已经进行了相当广泛的搜索，并找到了很多关于此的主题，这些主题帮助我使我的代码达到了它的目的，但我陷入了僵局，因为我不确定我在这里做错了什么。我正在尝试创建一个临时表，以便我可以从其他几个表中提取信息，将其放入，然后将其推出到 excel。现在我只是想从临时表创建一个记录集来检查它是否正在创建，我在 select * from 临时表行上收到错误。
我得到的错误是：error -2147217865 (800040e37) Invalid object name '#TempGetBOM'

The database platform is MS SQL Server.

数据库平台是 MS SQL Server。

EDIT: I updated the code to insert some data into the table to see if that would give me the object doesn't exist error. It executed without error, so that would indicate it exists. Then, while stepping through the code, I tried to execute the "create table" statement again, and it did error telling me the object already exists.

编辑：我更新了代码以将一些数据插入表中，看看这是否会给我对象不存在错误。它执行没有错误，所以这表明它存在。然后，在逐步执行代码时，我尝试再次执行“创建表”语句，但它确实错误地告诉我对象已经存在。

One thing I also found is after I create the table, if I step back in the code and run the line to check if the table exists and drop it, when I get to the create table line after that, it still says the table exits. So obviously that check and drop if exists statement is not working as I intended.

我还发现的一件事是在我创建表之后，如果我退回代码并运行该行以检查表是否存在并删除它，当我之后到达创建表行时，它仍然说表退出. 很明显，检查和删除 if exists 语句没有按我的意图工作。

Updated code:

更新代码：

'Declare variables
    Dim strSQL                      As String
    Dim strConBase                  As String
    Dim ADOcon                      As ADODB.Connection
    Dim ADOrsetA                    As ADODB.Recordset
    Dim ADOcmA                      As ADODB.Command
'Initialize objects
    Set ADOcon = New ADODB.Connection
    Set ADOrsetA = New ADODB.Recordset
    Set ADOcmA = New ADODB.Command
'Ensure clean exit if there is an error
    'On Error GoTo CleanExit

'Open Connections
    strConBase = strConDriver & strConServer & strConApp & "WSID=" & Environ$("COMPUTERNAME") & ";" & strConDbTest & strConNetwork & strConTConn
    ADOcon.ConnectionString = strConBase
    ADOcon.Open
'Open Recordsets for item
    strSQL = "SELECT '" & strItem & "'," & strTblItem & ".Item_desc_1," & strTblItem & ".Item_desc_2 FROM " & strTblItem & " WHERE Item_no ='" & strItem & "'"
    Set ADOrsetA.ActiveConnection = ADOcon
    ADOrsetA.Open strSQL

'Create a temporary table to handle the intermediary work between item and BOM recordsets
    With ADOcmA
        Set .ActiveConnection = ADOcon
        .CommandType = adCmdText
        .CommandText = "IF OBJECT_ID('#MyTempGetBOM') IS NOT NULL DROP TABLE #MyTempGetBOM"
        .Execute
        .CommandText = "CREATE TABLE #MyTempGetBOM (ITEM VARCHAR(255),DESCRIP1 CHAR(255),DESCRIP2 CHAR(255), LEV INT, SEQ INT, FLAG1 CHAR(255), PRIMARYKEY INT IDENTITY(1,1) PRIMARY KEY,QTY_PER NUMERIC)"
        .Execute
        .CommandText = "Insert Into #MyTempGetBOM (ITEM,DESCRIP1,DESCRIP2,LEV,SEQ,FLAG1,QTY_PER) select '" & strItem & "',Item_desc_1,Item_desc_2,1,'1','o',1 FROM " & strTblItem
        .Execute
    End With

    Dim ADOrsetB As ADODB.Recordset
    Set ADOrsetB = New ADODB.Recordset
    strSQL = "SELECT * FROM #MyTempGetBOM"
    Set ADOrsetB.ActiveConnection = ADOcon
    ADOrsetB.Open strSQL

回答by CBRF23

So, I got it working by changing the table name from #TempBom to MyTempBom. Not sure what the # is, I saw it used in some code I was trying to emulate.

因此，我通过将表名从 #TempBom 更改为 MyTempBom 来使其工作。不确定 # 是什么，我看到它在我试图模拟的一些代码中使用。

Obviously, I have very little experience in SQL.

显然，我在 SQL 方面的经验很少。

回答by CBRF23

OK, so after some more reading and searching, I decided to try a different approach to setting the recordset using the command object I created. This worked.

好的，所以经过更多的阅读和搜索之后，我决定尝试一种不同的方法来使用我创建的命令对象设置记录集。这奏效了。

I guess I don't understand the MS SQL object model well enough, but I thought the temporary table would be tied to the connection object, so that as long as I was using the same connection I could access it. But it appears to me it's actually tied to the command object. Is that right?

我想我不太了解 MS SQL 对象模型，但我认为临时表将绑定到连接对象，因此只要我使用相同的连接，我就可以访问它。但在我看来，它实际上与命令对象相关联。那正确吗？

Anyways, here is the updated code that is mostly working (the drop table if object not null statement still doesn't do what I expected). I'm sure it could be done in a better/simpler/more efficient way, but this is where I've gotten so far.

无论如何，这里是更新后的代码，它主要是有效的（drop table if object not null 语句仍然不符合我的预期）。我确信它可以以更好/更简单/更有效的方式完成，但这就是我到目前为止所取得的成就。

    'Declare variables
    Dim strSQL                      As String
    Dim strConBase                  As String
    Dim ADOcon                      As ADODB.Connection
    Dim ADOrsetA                    As ADODB.Recordset
    Dim ADOcmA                      As ADODB.Command
    Dim ADOrsetB                    As ADODB.Recordset
'Initialize objects
    Set ADOcon = New ADODB.Connection
    Set ADOrsetA = New ADODB.Recordset
    Set ADOcmA = New ADODB.Command
    Set ADOrsetB = New ADODB.Recordset
'Ensure clean exit if there is an error
    'On Error GoTo CleanExit

'Open Connections
    strConBase = strConDriver & strConServer & strConApp & "WSID=" & Environ$("COMPUTERNAME") & ";" & strConDbTest & strConNetwork & strConTConn
    ADOcon.ConnectionString = strConBase
    ADOcon.Open
'Open Recordsets for item
    strSQL = "SELECT '" & strItem & "'," & strTblItem & ".Item_desc_1," & strTblItem & ".Item_desc_2 FROM " & strTblItem & " WHERE Item_no ='" & strItem & "'"
    Set ADOrsetA.ActiveConnection = ADOcon
    ADOrsetA.Open strSQL

'Create a temporary table to handle the intermediary work between item and BOM recordsets
    Set ADOrsetB.ActiveConnection = ADOcon
    With ADOcmA
        Set .ActiveConnection = ADOcon
        .CommandType = adCmdText
        .CommandText = "IF OBJECT_ID('#MyTempGetBOM') IS NOT NULL DROP TABLE #MyTempGetBOM"
        .Execute
        .CommandText = "CREATE TABLE #MyTempGetBOM (ITEM VARCHAR(255),DESCRIP1 CHAR(255),DESCRIP2 CHAR(255), LEV INT, SEQ INT, FLAG1 CHAR(255), PRIMARYKEY INT IDENTITY(1,1) PRIMARY KEY,QTY_PER NUMERIC)"
        .Execute
        .CommandText = "Insert Into #MyTempGetBOM (ITEM,DESCRIP1,DESCRIP2,LEV,SEQ,FLAG1,QTY_PER) select '" & strItem & "',Item_desc_1,Item_desc_2,1,'1','o',1 FROM " & strTblItem
        .Execute
        .CommandText = "SELECT * FROM #MyTempGetBOM"
        Set ADOrsetB = .Execute
    End With

回答by BrupieD

Your problem is that the SQL Server session your temporary table is created in is terminated when your procedure completes.

您的问题是当您的过程完成时，创建临时表的 SQL Server 会话终止。