title: Need help creating temporary table using ADO from VBA
body: <p>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.<br>
  The error I'm getting is: error -2147217865 (800040e37) Invalid object name '#TempGetBOM'</p>
  
  <p>The database platform is MS SQL Server.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Updated code:</p>
  
  
  
  <pre class="lang-vb prettyprint-override"><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 &amp; strConServer &amp; strConApp &amp; "WSID=" &amp; Environ$("COMPUTERNAME") &amp; ";" &amp; strConDbTest &amp; strConNetwork &amp; strConTConn
      ADOcon.ConnectionString = strConBase
      ADOcon.Open
  'Open Recordsets for item
      strSQL = "SELECT '" &amp; strItem &amp; "'," &amp; strTblItem &amp; ".Item_desc_1," &amp; strTblItem &amp; ".Item_desc_2 FROM " &amp; strTblItem &amp; " WHERE Item_no ='" &amp; strItem &amp; "'"
      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 '" &amp; strItem &amp; "',Item_desc_1,Item_desc_2,1,'1','o',1 FROM " &amp; 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
  </code></pre>