Insert into an access table from SQL using recordset

Question

I am using the following code to insert data in an access table from SQL using a recordset. This table is further used for other operations. While this is inserting the data into the table perfectly, the time taken by this huge. Should I use any other method to insert data into the table to reduce the time taken?

Do Until rs.EOF
          DoCmd.RunSQL "INSERT INTO Table (Alpha,Beta,Gamma) VALUES(" & _
          rs.Fields(0).Value & ",'" & rs.Fields(1).Value & "'," & rs.Fields(2).Value _
          & " );"
        rs.MoveNext
Loop

Are you inserting identical data into every row? Why do you keep doing it in a loop? Just run the sql once, you are running the same sql for every row in a table. — Fionnuala
– Fionnuala, Commented Mar 3, 2014 at 14:16
yes I am doing it in a loop. But what would be the best way so that I can insert the data in a single shot. — Nishith
– Nishith, Commented Mar 4, 2014 at 6:37

Nick.Mc · Accepted Answer · 2014-03-04 10:45:48Z

1

Create a linked table to the SQL table, say it's called MyLinkedTable
Create an Append query to append to your local Access table from your linked table. It will look something like this: INSERT INTO MyAccessTable (Field1,Field2...) SELECT Field1,Field2... FROM MyLinkedTable;

answered Mar 4, 2014 at 10:45

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

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

Comments

E Mett · Accepted Answer · 2014-03-04 09:30:06Z

0

If you could select the data in the SQL UPDATE statement instead looping in VBA it would take a fraction of the time as all the work would be done by the server side.

answered Mar 4, 2014 at 9:30

E Mett

2,3223 gold badges19 silver badges39 bronze badges

7 Comments

Nishith Over a year ago

is it possible to do that is vba. My table is in access. This table is used for other operations.

E Mett Over a year ago

The whole idea is that don't want to do this in VBA because it takes too long. You want do it all in SQL. Tell me, is the source table in the same database as the destination table?

Nishith Over a year ago

Source database is in SQL but destination table is in access.

E Mett Over a year ago

Do you mean SQL Server? How do you connect to the source table?

Nishith Over a year ago

Yes SQL Server. Apologies for confusion. I am connecting to SQL Server using ADODB.

|

Séb Cô · Accepted Answer · 2017-03-13 20:45:38Z

0

You just need to do an INSERT INTO SELECT. We need more information on your RecordSet but you probably do not need it.

'Here we go, with just one line! DoCmd.RunSQL "INSERT INTO Table (Alpha,Beta,Gamma) SELECT column1, column2, column2 FROM YourTable" The SELECT statement is probably the same as the one you used for opening your Recordset. Good luck!

answered Mar 13, 2017 at 20:45

Séb Cô

891 silver badge8 bronze badges

1 Comment

Mike Over a year ago

This will only work if "YourTable" is a linked table to the SQL Server.

Collectives™ on Stack Overflow

Insert into an access table from SQL using recordset

3 Answers 3

Comments

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related