title: Importing data from SQL Server to Access 2010
body: <p>I am trying to collect specific data from a series of tables in a SQL Server database and combine them together in a single table in Access using VBA.  </p>
  
  <p>I created a recordset in Access using a query that combines all the data using multiple UNION ALL statements.  My initial approach was to iterate through the recordset with something like:</p>
  
  <pre><code>Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim SQL As String
  
  cnn.Open "Driver={SQL Server};Server=" + SQLServerNameStr + ";Database=" + DBNameStr + ";Trusted_Connection=Yes"
  
  SQL = "SELECT A, B, C FROM TableA UNION ALL SELECT D, E, F FROM TableB UNION ALL ..." // etc., etc., etc.
  
  rs.Open SQL, cnn, adOpenForwardOnly
  
  While Not rs.EOF
      CurrentDB().Execute ("INSERT INTO AccessTable VALUES ('" &amp; rs("FieldA") &amp; "', '" &amp; rs("FieldB") &amp; "', '" &amp; rs("FieldC") &amp;"')")
      rs.MoveNext
  Wend
  </code></pre>
  
  <p>This all works, however, it is excruciatingly slow for approximately 20,000 records.  I am sure there is a better way ... probably by building the INSERT into the main query, however, I cannot wrap my head around how to do this when the data source is on a connection to SQL Server and the destination in the current database.</p>
  
  <p>Any suggestions would be greatly appreciated.</p>