Insert into an access table from SQL using recordset

1
votes

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
3
sqlms-accessvbams-access-2010
Can you please elaborate your code – Mohammad Ashfaq
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
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

3 Answers

1
votes
  1. Create a linked table to the SQL table, say it's called MyLinkedTable
  2. 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;
0
votes

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.

0
votes

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!