I was searching the web for 2 days and didn't find the answer, maybe you can help me.
Users of my DB front-end use the form UI to filter some data (the data behind the form is a recordset generated by stored procedure). Now, I would like to be able to use INSERT statement for the data (say, a note or comment in a separate "comment" table). What would be the best way of doing that?
Originally, my though was that the EASIEST way of doing that would be a VBA DO loop and use an SQL INSERT, but in extreme cases that would mean hundreds or thousands of INSERT queries.
I thought of a rather unorthodox method - create a "virtual" Excel app with a Table, assign a said recordset to that table and then use TransferSpreadsheet to convert the Excel table to Access Table and then use the temp table in INNER JOIN for the single INSERT query. It's slower than the cycle method if it's a matter of a few dozens of records, it's much faster if it's hundreds or thousands, however. But of course, this solution is rather goofy.
I would like to convert ADODB Recordset to local table, preferably without cycles. Or, user the recordset as some kind of filter in standard SQL INSERT.