Append ADO recordset to table

[cb22]

I have created a recordset built from scratch, based on data from an
external, non-database source. I want to append this recordset to a
table in an Access database without using a loop to traverse through
each record and add to the table.

Any suggestions on what I can use or do to make this possible?

Regards.

[Lyle Fairfield]

Re: Append ADO recordset to table

cb22 wrote:[color=blue]
> I have created a recordset built from scratch, based on data from an
> external, non-database source. I want to append this recordset to a
> table in an Access database without using a loop to traverse through
> each record and add to the table.
>
> Any suggestions on what I can use or do to make this possible?
>
> Regards.[/color]

You can create your recordset by using a simple SQL statement such as

"SELECT * FROM TableName WHERE 1=2"

which will give you an empty recordset.

You can detach this recordset by setting its ActiveConnectio n property
to Nothing.

You can then add your data from the external, non-database source,
using the same methods/code/whatever that you have already created.

You can then re-attach your recordset by resetting its ActiveConnectio n
back to whatever it was.

You can then use UpdateBatch to add the new records.

Of course you will have to set CursorLocation, CursorType and LockType
appropriately. And there may be no advantage to detaching the
recordset, depending upon your connection, preferences and needs.

My own preference is to use a series of INSERT INTO sql statements, one
for each row of data that you are getting from your external,
non-database source. While calling many SQL INSERTs might seem clumsy
or inefficient, in fact, this method is likely to be safer and quicker
than recordset manipulation and updating.

[cb22]

Re: Append ADO recordset to table

Thanks for the suggestion Lyle!

I will consider your advice.

:-)