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.

Your VBA code for your execute statement is incorrect, how does this even run? — enderland
– enderland, Commented May 6, 2014 at 19:17
I was trying to simplify ... I didn't actaully cut and paste my code ... I was just looking at how many mistakes I make when trying to code without an editor correcting my syntax!!! ;) — DRP
– DRP, Commented May 6, 2014 at 19:22

Jeremy Cook · Accepted Answer · 2014-05-07 20:28:19Z

1

Try using DoCmd.TransferDatabase* to transfer data from a SQL Server view, that queries the data the way you need it, into an Access table.

DoCmd.TransferDatabase acImport, "ODBC Database", _ 
"Driver={SQL Server};Server=" & SQLServerNameStr & ";Database=" & DBNameStr & _
";Trusted_Connection=Yes", acTable, "SomeSqlServerView", "AccessTable"

Hopefully the transfer database method does a good job of managing insertions and speeds up the import process.

*Changed my answer since batch updates don't work with Access 2010 as a data destination.

edited May 7, 2014 at 20:28

answered May 6, 2014 at 21:57

Jeremy Cook

22.4k11 gold badges74 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DRP Over a year ago

dbOptimisitcBatch / dbUpdateBatch are only supported for ODBCDirect workspaces ... can I open an native Access table in this way?

Jeremy Cook Over a year ago

Ah, you're right, batch updates do not seem to be supported. Now that I'm thinking about it I was writing to SQL Server from a VB6 application.

Maury Markowitz · Accepted Answer · 2014-05-06 21:43:30Z

0

I haven't used native tables so much, but I suspect the issue is related to inserting one at a time.

You might want to try doing the insert through SQL and doing 50 rows at a time or so.

answered May 6, 2014 at 21:43

Maury Markowitz

9,34412 gold badges55 silver badges110 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:45:22Z

0

Why not try doing an Insert query instead creating a recordset? See this question: How to insert "Entire" DAO recordset into a table with VBA

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered May 6, 2014 at 22:02

John Go-Soco

9541 gold badge10 silver badges22 bronze badges

4 Comments

DRP Over a year ago

This is what I was hoping to do, but I cannot figure out how to wrap the SELECT query, which is on a SQL Server connection, inside the INSERT query that is on the CurrentDb()

John Go-Soco Over a year ago

INSERT INTO AccessTable (FieldA, FieldB, FieldC) SELECT (FieldA, FieldB, FieldC) FROM (SELECT A, B, C FROM TableA UNION ALL SELECT D, E, F FROM TableB UNION ALL ..." // etc., etc., etc.)

DRP Over a year ago

Maybe I am being think (it wouldn't be the first time) but I am going to run the query by calling CurrentDB().Execute so the INSERT will go into a table in the Access database, but how do I then tell the query that the SELECT statement is coming from the SQL Server using the ADODB (or some other type) connection, not the currentdb?

John Go-Soco Over a year ago

One way to do it would be to import all the source tables into your Access DB as linked tables, or even copy all the data across.

Collectives™ on Stack Overflow

Importing data from SQL Server to Access 2010

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related