title: excel vba run sql query on entire array
body: <p>I have an sql query that is running in a loop in excel.  It works while the array is small.  However the array is now up to around 4000 items and it causes excel to crash.  Is there a better way to do this, am I missing some easy way to do this</p>
  
  <pre><code>For k = 0 To ubound(sqlarray)
  
  Dim conn As ADODB.Connection
  Dim recst As ADODB.Recordset
  Dim sqlstring As String
  
  Set conn = New ADODB.Connection
  Set recst = New ADODB.Recordset
  
  
  conn.ConnectionString = "Driver={Client Access ODBC Driver (32-
  bit)};System=mysys ;NAM=1;CurrentSchema=myschema;"
  conn.Open
  
  
  Sql = "Select ACCOUNT_NBR, ASSIGNED_TO from prodlib.gr_exp_account where 
  account_nbr in ('" &amp; sqlarray(k) &amp; "' )"
  
  Set recst.ActiveConnection = conn
  
  recst.Open Sql, conn
  Sheets("recst").Range("A" &amp; k).CopyFromRecordset recst
  
  
  conn.Close
  
  Application.ScreenUpdating = True
  Next k
  
  End Sub
  </code></pre>