Insert into the local access table from the external database

[Jun]

Friends,
I need to insert around 13000 records from a sybase external DB to one
of my local table in access. I have the connection string set up and
everything works fine except for the execution time. It takes a lot of
time to populate the local table because I have a recordset variable
which loops through the 13000 records returned from the ADODB
connection object and inserts one line after the other into the local
table. Do you have any better alternative to approach the time issue?
Any help in this regard will be great.

 

[John Vinson]

Friends,
I need to insert around 13000 records from a sybase external DB to one
of my local table in access. I have the connection string set up and
everything works fine except for the execution time. It takes a lot of
time to populate the local table because I have a recordset variable
which loops through the 13000 records returned from the ADODB
connection object and inserts one line after the other into the local
table. Do you have any better alternative to approach the time issue?
Any help in this regard will be great.

Have you tried just running an Append query? Link to the sybase table,
and create an Append query based upon it, appending to the local
table. Run it. This will typically be much faster than looping through
a recordset in code!

John W. Vinson[MVP]

 

[Jerry Whittle]

What John said! If you are stuck looping through records and inserting one
at a time, removing indexes can speed things up. You might even want to
insert into a temporay table with no indexes, primary key, validation rules,
subdata sheets, or foreign keys then append records from the temp table in to
the final table.