title: VBA: Oracle SQL Insert Query Not Inserting Data
body: <p>Apologies if this is too much info, but I wanted to be thorough when posting. </p>
  
  <p>I've been struggling with an issue getting my temporary table to populate with data from an Insert Query.</p>
  
  <ul>
  <li><p>This data is being pulled from Oracle SQL tables</p></li>
  <li><p>This query creates the temp table, inserts the data, and queries the temp table without issue in the Oracle SQL Developer software</p></li>
  <li><p>My code will successfully create the temp table (I've verified its existence in the Oracle software)</p></li>
  <li><p>I am able to use Select Queries with my setup (So I do not believe the connection is the issue)</p></li>
  <li><p>Running my code doesn't produce any errors (At least nothing that stops the execution) </p></li>
  </ul>
  
  <p>What I did notice is that if I use "Record Set" in the execution of my insert query, it does show that there are the expected amount of affected rows, but when I Select All (From the Oracle Software) from the temp table it shows nothing populated.</p>
  
  <p>I've been looking all over but I haven't quite figured out what I am missing. From what I was reading it could be that I don't Set NOCOUNT ON (Or Set FEEDBACK OFF), but I can't find how to actually do that within my VBA code. I originally tried inserting the data into an existing table, so I attempted to create a temporary table to move the data into.</p>
  
  <p>Option Explicit</p>
  
  <p>Sub oracleQuery()</p>
  
  <pre><code>Dim ws1 As Worksheet
  Dim ws2 As Worksheet
  Set ws1 = ActiveWorkbook.Worksheets("Query Output")
  Set ws2 = ActiveWorkbook.Worksheets("Main Page")
  </code></pre>
  
  <p>Clears query output worksheet</p>
  
  <pre><code>ws1.Cells.Clear
  </code></pre>
  
  <p>Assigns variables</p>
  
  <pre><code>Dim cnn As New ADODB.Connection
  Dim rst As New ADODB.RecordSet
  Dim ConnectionString As String
  Dim StrQuery As String
  Dim StrQuery2 As String
  Dim StrQuery3 As String
  Dim StrQuery4 As String
  </code></pre>
  
  <p>This query creates a blank Global Temp table (With column headers)</p>
  
  <pre><code>StrQuery = "XXXXXX"
  </code></pre>
  
  <p>This is my insert Query</p>
  
  <pre><code>StrQuery2 = "INSERT INTO temp_1 (Field1,Field2,Field3,..etc) Select From 
        Where UNION ALL Select From Where UNION ALL..Etc
  </code></pre>
  
  <p>This query selects the data from the temp table, and joins in some addition fields with detail information</p>
  
  <pre><code>StrQuery3 = "Select temp_1. Etc....
  </code></pre>
  
  <p>This query is used to drop the temp table when I am done with it</p>
  
  <pre><code>StrQuery4 = "DROP TABLE temp_1"
  </code></pre>
  
  <p>This is my connection string</p>
  
  <pre><code>ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=(XXX)(CONNECT_DATA=(SERVICE_NAME=XXX)));User ID=XXX;Password=XXX"
  </code></pre>
  
  <p>Opens connection to the database, also sets a timeout timer</p>
  
  <pre><code>cnn.Open ConnectionString
  cnn.CommandTimeout = 60
  </code></pre>
  
  <p>Executes Temp table query</p>
  
  <pre><code>cnn.Execute StrQuery
  </code></pre>
  
  <p>Executes Insert Query </p>
  
  <pre><code>cnn.BeginTrans
  cnn.Execute StrQuery2
  cnn.CommitTrans
  </code></pre>
  
  <p>Executes select query on temp table, uses a record set</p>
  
  <pre><code>rst.Open StrQuery3, cnn
  </code></pre>
  
  <p>Drops temp table</p>
  
  <pre><code>cnn.Execute StrQuery4
  </code></pre>
  
  <p>Dumps all the results from the record set into cell A2 of the first sheet in the active workbook</p>
  
  <pre><code>ws1.Range("A2").CopyFromRecordset rst
  </code></pre>
  
  <p>Empties connection string</p>
  
  <pre><code>ConnectionString = ""
  </code></pre>
  
  <p>Closes and clears recordset</p>
  
  <pre><code>rst.Close
  Set rst = Nothing
  </code></pre>
  
  <p>End Sub</p>
  
  <p>It would be great to determine what is actually causing my Insert Query to not insert data. I would greatly appreciate any insight on this issue as I am running out of ideas to check.</p>
  
  <p><strong><em>UPDATE</em></strong> I simplified my code to hone in on my problem. My issue is the INSERT INTO Query is not actually inserting any data into the table. The Select Query I am trying to Insert results in 3 columns &amp; rows of data.</p>
  
  <p>Temp table query</p>
  
  <pre><code>StrQuery = "CREATE GLOBAL TEMPORARY TABLE temp_1( GALOTIDX Number(9, 0), LOTID VARCHAR2(12 BYTE), LOTDESCR VARCHAR2(40 BYTE))"
  </code></pre>
  
  <p>INSERT INTO Query String</p>
  
  <pre><code>StrQuery2 = "Insert INTO temp_1 (GALOTIDX, LOTID, LOTDESCR)"
  StrQuery2 = StrQuery2 + " Select LotIdx, ID1, Descr1 From DataTable1"
  </code></pre>
  
  <p>Query For Final Select</p>
  
  <pre><code>StrQuery3 = "Select * From temp_1"
  </code></pre>
  
  <p>Query for Dropping Temp Table</p>
  
  <pre><code>StrQuery4 = "DROP TABLE temp_1"
  </code></pre>
  
  <p>Builds connection string</p>
  
  <pre><code>ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=XXX)(Port=XXX))(CONNECT_DATA=(SERVICE_NAME=XXX)));User ID=XXX;Password=XXX"
  </code></pre>
  
  <p>Opens connection to the database, also sets a timeout timer</p>
  
  <pre><code>cnn.Open ConnectionString
  cnn.CommandTimeout = 60
  </code></pre>
  
  <p>Creates Temp Table</p>
  
  <pre><code>cnn.Execute StrQuery
  </code></pre>
  
  <p>Executes Insert Into Query</p>
  
  <pre><code>Dim recordsAffected As Integer
  
  cnn.BeginTrans
  cnn.Execute StrQuery2, recordsAffected
  cnn.CommitTrans
  </code></pre>
  
  <p>Selects Data from temp table and dumps into a worksheet</p>
  
  <pre><code>rst.Open StrQuery3, cnn
  ws1.Range("A2").CopyFromRecordset rst
  </code></pre>
  
  <p>Drops Temp Table</p>
  
  <pre><code>cnn.Execute StrQuery4
  </code></pre>
  
  <p>Empties connection string</p>
  
  <pre><code>ConnectionString = ""
  cnn.Close
  </code></pre>