Loading tables from a recordset

[Grodon]

I'm creating an Access db that will retrieve recordsets
from an SQL Server db, then load the data into a table.
Sometimes the recordsets will contain several hundred
records, maybe even a thousand. What's the best way to
load these records into the Access table? If I loop
through the recordset and append the records one at a
time, I'm thinking this may take too long for large
recordsets.

thanks in adv.
Grodon

 

[Brendan Reynolds \(MVP\)]

It could be slow for large recordsets, yes. But a thousand records is a
small recordset. Unless you're going to be dealing with tens of thousands of
records, you probably won't see much difference between alternative methods.

One alternative is to link the SQL Server tables and use append queries to
transfer the data. Another is to use the DTS (Data Transformation Services)
tool that comes with SQL Server.