Copying ADO recordsets

I know I can not be the first to be doing this !
I am simply combining recordsets of the same type from multiple MDBs.

I have a 'virtual' recordset

Dim myRst as new adodb.recordset

I use the Fields,append collection method to generate the field names.

Open the myRST, then start setting values from my source recordset by iterating through each record and poking each individual field value into myRST.


I get the next mdb, generate the recordset and continue adding to myRST using the same methods.

Now...this take bloody ages.

Anyway to update the myRST with at least one record at a time in one hit rather the cycling through each records fields.

I have seen myRST.Update (Fields,Values) where you can pass an array of field names and an array of field values in one hit to store a record. What is then a convenient way of generating these arrays from my source recordset.


Appreciate any help or suggestions (..what..pack up and go home ?)

I am sure this is a common scenario..I have solution but it is very slow...

So how can I achieve this more efficiently ??

Thanks

Looks like you could try the GetRows method of the recordset. Supposedly twice as fast, who knows?
This gets the records into a 2D variant array. Then you can try it with your update function.
ArrayName = RecordsetName.GetRows(Rows, Start, Fields)
Rows: optional long (number of records to retrieve)
Start: Optional bookmark for where to begin
Fields: A variant array of field names or ordinal numbers or a single name or ordinal.

Also look up the VB help on this.

Thanks Ralph.

When passing a 2D array to the update method which expects (fields(),values()), is there a way of passing 1 dimension of the 2D array ?

Here's hoping.

Thanks

Well, it would be nice if there was a way to assign 1D chuncks of a 2D array in vb. I'd have to research that. In the meantime looping the column values into value() is doable like this (untested):

For i = LBound(ArrayName, 1) To UBound(ArrayName, 1)
For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
Value(y) = ArrayName(i, y)
Next y
myRST.Update(fields,Value)
myRST.Addnew??????
Next i

Thanks Ralph.

Things are getting faster !

As you probably know you only need to generate a column string array once and then pass this as your columns so you can get rid of one of the loops.

Thanks for the inspiration.

If you find out a way of getting rid of those loops altogether let me know.

Thanks