Transferring large amounts of data into Access 2003

[ewanm]

Hello,

I'm trying to transfer data from an Oracle database into an Access 2003 database using VB code. I need to set this up so that it can be ran on a regular basis and I really would like to avoid using linked tables if possible.

I can open 2 ADO recordsets (one from the Oracle database and the current Access database) and write from one recordset to another by moving through the records one at a time. I've tried .Update and .UpdateBatch and it works but this seems to be slower than when I used an append query and linked tables. There's approximately 200,000 records to transfer although each record contains a few small fields.

Is there any way to copy a recordset into an Access database without writing it line by line?

Any advice or suggestions on this would be much appreciated

[FishVal]

Hi, ewanm.

To the best of my knowledge there is no opportunity to substantially enhance the speed of RecordSet-to-RecordSet data transfer.
A minor improvement may be achieved with setting source RecordSet .CursorType to forward-only and using slightly faster DAO.Recordset as destination. But this will never be as fast as SQL command.
What prevents you from using linked table and append query ?