copy contents of recordset to a table

[mustang123]

Hi,

I get data from sybase server using ADO Connection and disconnected recordset in my access Database.

Now I have a disconnected recordset. I want to insert the contents of this recordset into a Access table. I can loop through the recordset and insert record by record. But I want to do a bulk insert/Update assuming the columns are the same in the recordset and the table.

How can I copy a recordset data to a table in one shot?

The recordset is a disconnected recordset which was queried from another database.

Thanks
mustang123

[NeoPa]

What do you mean by a disconnected recordset?
I know of tables and queries as objects and (ADODB & DAO) recordsets in code.
My answer (if I were sure I understood the question correctly) would be to change the SQL of the recordset slightly so that it's an append query. It's hard to be clearer with so little detailed information though.

[jdb112]

My guess is that mustang123 means a that recordset was created by running a query on an external database.

After creating the recordset, the connection to the external database may or may not have been closed; the point is, the recordset is now a "stand-alone" set of data: you cannot bring it to a new table by referencing the original data source.

Mustang wants to copy the records from this recordset to a separate table in Access. Seems pretty straightforward to me.

The problem is...I don't know how to do this, either. I'd like to, and that's how I found this question, by Googling "copy a recordset to a table".

Mustang, maybe some variation on this will work for you: I tried using a statement that would use the external (DB2, in my case) query as part of an INSERT command on the Access table:

INSERT INTO [AccessTable] [SELECT blahblahblah from DB2table]

Needless to say, this hackneyed approach did not work. I'll see if I can find something else.

And for the record, I know an option is to just loop through the recordset (pulled from DB2), inserting one row at a time into the Access table. But, as mustang123 has probably found out, it's too slow. A bulk operation, if it could be finagled, would be great. I'll let you know if I find anything...

Jared