title: Retrieve data from Oracle 11g to Excel 2007 - Excel not responding
body: <p>Houston, we have a problem! :)</p>
  
  <p>I have some Excel files that connect through VBA to an Oracle 11g database using the OraOLEDB driver:</p>
  
  <pre><code>strConn = "User ID=&lt;useridhere&gt;" &amp; _
      ";Password=&lt;passwordhere&gt;" &amp; _
      ";Data Source=" &amp; xServer &amp; _
      ";Provider=OraOLEDB.Oracle"
  </code></pre>
  
  <p>The problem happens when I run a SQL query like <code>SELECT * FROM &lt;view_name_here&gt;</code>. These queries run when the user clicks a button - that has a macro attached.</p>
  
  <p>What I'm using to do the queries:</p>
  
  <pre><code>Sub Load_Data(ws As Worksheet, sSql As String)
      Dim oQt As QueryTable
      OpenConn
      Dim rs As ADODB.Recordset
      Set rs = New ADODB.Recordset
      rs.Open sSql, sConn
  
      ws.Activate
      ClearActiveSheet
  
      Set oQt = ActiveSheet.QueryTables.Add(rs, ws.Cells(1, 1))
      oQt.Refresh
      rs.Close
      sConn.Close
  End Sub
  </code></pre>
  
  <p>Note: <code>ws</code> is the worksheet that will display the data, <code>sSql</code> is the query to retrieve the data.</p>
  
  <p>My problem is that Excel freezes - not responding - while retrieving data from the Oracle database - if I have 10 instances of Excel open, all will be unusable.</p>
  
  <p>My objective is to have a "non-blocking" database access - if the query takes 10 minutes, I can still use excel.</p>
  
  <p>If anyone can help me, that would be great :)</p>
  
  <p>Note: I've attached all the pieces of code that I thought required. If you want to see anything else that can lead to a solution, just let me know.</p>
  
  <p>Thanks</p>
  
  <p><strong>EDIT:</strong> I've managed to get it working with the following code:</p>
  
  <pre><code>Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=" &amp; Server &amp; ";" &amp; "USER ID=" &amp; UID &amp; ";PASSWORD=" &amp; PWD
  Cmd.ActiveConnection = Conn
  Cmd.CommandType = adCmdText
  Cmd.CommandText = sSql
  
  Set RS = Cmd.Execute
  
  For X = 0 To noLines
      Data.Cells(1, X + 1) = RS.Fields(X).Name
      Next
      Do While Not RS.EOF
          Row = Row + 1
          For Findex = 0 To RS.Fields.Count - 1
          Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
          Next Findex
          RS.MoveNext
      Loop
  
  Application.Calculation = xlCalculationAutomatic
  Application.Calculate
  
  Conn.Close
  </code></pre>
  
  <p>This <strong>does</strong> block the <em>current</em> file but not the remaining ones. That was the main goal.</p>