Transfer ADO recordsets to Excel or Access

[m261567]

I already have a VBA macro developed that develops a SQL statement based on variable criteria and queries an Oracle database and returns an ADO recordset. I then paste the data into an open Execl workbook using the CopyFromRecordset command. This works great. However, idealy I would like to export the recordset to a CLOSED workbook that resides on a network server. Is there a way to open two connections (one to the Oracle server and another to the closed Excel workbook) and simply transfer the recordset from one to the other. That would make life wonderful.

Another thing that I would like to be able to do is transfer the same recordset to a new Access table.

Oh, by the way. Criteria for the query changes. They will return different fields, depending on the need.

Thanks for any help.

 

[wbeetge]

What are you using to connect? Data Controls, Code, Classes?
You would have two references to the connection object -

What I would do is the following: (Rs1 on Conn1 and Rs2 on Conn2)
___________________________________________
Dim nField as integer

Do while not Rs1.eof
For nField = 0 to Rs1.Fields.Count - 1
Rs2(nField) = Rs1(nField)
Next
Rs2.update
Rs1.Movenext
loop
___________________________________________


This will copy all data to the same table structure from the one connection to the other.

You can at any time create a connection object to any datasource, you can use the same recordset for both connections, as the SET Rs refreshes what it contains.
All you need to do is to set the active connection in the Rs to the required connection object