title: Run multiple SQL queries (Oracle) using VBA - why so slow?
body: <p>I wrote a simple VBA code to run multiple SQL queries. The results are supposed to go to excel sheet. The problem is that the procedure takes ages to finish! In fact it would be much faster to run these queries one by one. Can anyone please tell me how to make it work faster?</p>
  
  <p>Here is my code:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Const strCon As String = "Driver={Microsoft ODBC for Oracle}; " &amp; _
  "CONNECTSTRING=(DESCRIPTION=" &amp; _
  "(ADDRESS=(PROTOCOL=TCP)" &amp; _
  "(HOST=xxxx)(PORT=xxx))" &amp; _
  "(CONNECT_DATA=(SID=RTD))); uid=xxxx; pwd=xxxx;"
  
  Sub RunScripts()
  
  Dim r As Integer
  Dim con As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim q1 As String
  Dim q2 As String
  Dim q3 As String
  
  Set con = New ADODB.Connection
  Set rs = New ADODB.Recordset
  
  q1 = Worksheets("Data").Range("i28").Value
  Set rs = con.Execute(q1)
  Worksheets("ACCV Query").Range("A2").CopyFromRecordset rs
  
  Set rs = Nothing
  r = Worksheets("ACCV Query").Range("A65536").End(xlUp).Row
  
  'Next SQL Query
  q2 = Worksheets("Data").Range("j28").Value
  Set rs = con.Execute(q2)
  Worksheets("ACCV Query").Range("A" &amp; r).CopyFromRecordset rs
  
  Set rs = Nothing
  r = Worksheets("ACCV Query").Range("A65536").End(xlUp).Row
  
  'Next SQL Query
  q3 = Worksheets("Data").Range("k28").Value
  Set rs = con.Execute(q3)
  Worksheets("ACCV Query").Range("A" &amp; r).CopyFromRecordset rs
  
  con.Close
  Set con = Nothing
  Set rs = Nothing
  
  End Sub
  </code></pre>
  
  <p>The SQL queries are stored in cells and named q1, q2, etc. (all of them work).</p>
  
  <p>When I ran two of such queries it worked fine, but once I launched 15... the macro never finished working. Each SQL script takes couple of seconds to run so it should work quick.</p>
  
  <p>How can I make it work faster?
  Help please!</p>
  
  <p>Thanks a lot!
  M.</p>