Importing data from SQL Server to Access 2010

Question

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.  

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:

    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 ('" & rs("FieldA") & "', '" & rs("FieldB") & "', '" & rs("FieldC") &"')")

        rs.MoveNext

    Wend

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.

Any suggestions would be greatly appreciated.

Answer 1

I would create a pass-through query with the union SQL statement. Then create an append query with the p-t as the source.

Answer 2

Dynamically? Sure. You can set the SQL property of any querydef, including a passthrough query.

Answer 3

Thanks for your response, unfortunately I don't think this will work as this process needs to be run on many different SQL databases.  The user will select the server and database to perform the action upon.  The databases are all production databases of the same form, but I believe I would have to set up a pass through query every time this process was to be run, which is not practical.

Is there is a way to dynamically create the pass through query?

Answer 4

I had thought of that, but the problem with that idea is that this will need be run on many different production databases.  I don't think that I could convince the SQL Developers to add a View to the production code for thousands of databases that would only be used on a very small percentage of them once or twice by our team.

Answer 5

One alternative would be to create a VIEW in SQL Server and link to it from Access. Then your insert statement would be trivial: insert into myAccessTable select * from mySqlView