ADO Select Into Query (1 Viewer)

[honda882000]

Hello,

I am trying to connect to an Oracle database using ADO instead of DAO. I am connecting using ODBC, and can connect just fine, but I want to insert the records retrieved by the recordset into a table in my access database. Can anyone tell me how to accomplish this without looping throught the recordset and manually doing table inserts? I cannot seem to do SELECT...INTO since the SQL is talking directly to the server, and will not recognize my local table, correct?

Thank you very much.

 

[LPurvis]

You've correctly identified the issues.
Your recordset is a memory structure. It has no means of being accessed directly into the file based engine of the database. You have to use code to access that data and insert it into the table. You have to do that iteratively. (You could, consider persisting the recordset to XML, importing the XML file to the database and then into your table - but that's a real round about way of getting there which will likely uncur more overhead than iterating).

Your connection to the Oracle database is just that - an external connection.
It isn't aware of from where it originated. So any SELECT INTO is executed entirely on the server.
Hence you'd have to include Oracle-side functionality to push out to the table (I can't suggest how to do that in Oracle - even in SQL Server hitting an individual front end file from the server isn't going to be worth the effort).

Cheers.