Array to ADODB recordset

[danielreber]

I am creating an array from multiple recordsets and use the array to populate an activex control. I need to take that array and populate a recordset (I don't care how many steps there are from array to recordset) so I can persist the contents of the recordset to a RDBMS. I have have been trying on and off for a couple of years and I have not been able to figure it out. Does any body have an idea?

Thanks

Dan Reber

[doofusboy]

First of all, you can not "take that array and populate a recordset".

A recordset is what gets returned when you query a database.

If you want to combine several recordsets in an array and then send that data to a database, you would need to use something like ADO to create a connection to your database and then use SQL to input or update the data from your array to a table or tables in your database.

[danielreber]

I am using arrays because I am getting data from multiple recordsets that
are aggregates of

Invoices
Payments
Rejections

or what what ever else the user selects. I am then building an array that
is a combination of fields from each recordset and "join" them accross
fields with commom values. The activex control that I use allows arrays to
be pased to it as a datasource. What I need to do now is to take that
array and convert in to a recordset because I want to use this combined data
in another control that I use that only allows recordsets as a datasource.

PS - The data is for an analytical tool and not an OLTP tool.

Thanks

Dan

[pclement]

I'm assuming that you're only going to update a single table in the RDBMS through this Recordset. If so first create a new Recordset based upon this table, it can be empty if you wish. Then disconnect it by setting the ActiveConnection property to Nothing and use the Update method to add new rows from your array.

Once you've added all rows, reconnect the Recordset to the database (using the ActiveConnection property) and then call the UpdateBatch method to apply the updates to the database. Of course you need to check for any update conflicts that may occur during the batch update.

[danielreber]

No data in the database will be updated. My application is for analytical purposes only. Part of my application allows aggregates of parent and child data displayed at the same time without overstating the parent data. I do this by doing multiple passes over the databases and "joining" the aggregated data at the client and inserting the data into arrays. This works great for one part of my application because the activex control allows arrays as a datasource but another activex control only allows adodb recordsets.

I am thinking that I need to roll my own xml file the duplicates the persisted adodb recordsets xml file.

PS - I cannot use stored procedures in SQL Server because the datasets are 100% ad-hoc

[pclement]

OK, well that sounds a bit different than your original question where you indicated that you need to persist the contents of a Recordset to an RDBMS, so I'm still not exactly certain what you're attempting to do.

If you just want to persist the data, then it's rather easy to export to an xml file from an ADO Recordset.

[danielreber]

Paul,

I need to first get the data from an array to a recordset. When I said "...roll my own xml file..." I meant that I may have to take the array and create an xml file from that array. The xml file will need to be in a format that a recordset can open. Once the recordset opens the xml file I can then use it as a datasource for the other activex control. I can also save my rolled xml file to a RDBMS so it can be "cached" and used by multiple users.

If you have any other ideas to accomplish this (creating a duplicate file format of a persisted recordset to xml seems difficult) then that would be great.

Thanks

Dan

[Phil Weber]

Dan: See if this helps: http://msdn.microsoft.com/library/en...tml/vb04h4.asp

[danielreber]

That works great Phil, thanks. The only issue was that when I tried

oRS.Save App.Path & "\test.rst", adPersistADTG

I got the error "Requested conversion is not supported."

But when I used

oRS.Save App.Path & "\test.rst",adPersistXML

I was able to save to disk and load the file to a new recordset.

Thanks again for your help.

Dan