Reverse of GetRows? (Array to Recordset) 2

[ESquared]

Is there a way to stuff a two-dimensional variant array into a recordset, basically the opposite of GetRows? Or do I have to do this (pseudocode):

Set Rs = New ADODB.Recordset
Rs.Fields.Append Name1, Type, Length
Rs.Fields.Append Name2, Type, Length

Rs.Open
For LBound(Array) To UBound(Array)
   Rs.AddNew
   For Each Fld in Rs.Fields
      Fld.Value = Array(Rs.AbsolutePosition, Fld.<Index>)
   Next
   Rs.Update
Next

 

[SBerthold]

Well, if you had two single dimensioned arrays, one for the field names and one for the field values, then you could avoid using "For Each Fld in Rs.Fields", and add a record using a single .AddNew call. However, for this you would still need to loop through each record.

 

[ESquared]

Oh yeah, you can add a whole row at once, forgot about that, thanks.

 

[BobRodes]

Your for loop is a bit wonky, and you can use the syntax correction to streamline your code a bit:

For [COLOR=red]i = [/color]LBound(Array) To UBound(Array)
   With Rs
      .AddNew
      For j = 0 to .Fields.Count - 1
          .Fields(j) = Array(i, j)
      Next j
      .Update
   End With
Next i

 

[ESquared]

It was pseudocode to get the idea cross, not to demonstrate best practice. I wrote it as fast as was possible without any thought for optimum code.

Thanks for the help in any case!

 

[SBerthold]

This is what I had proposed to ESquared:

rs.AddNew Array(Field1,Field2), Array(Value1,Value2)

doing away with the fields loop.

 

[ESquared]

Yes that is what I had chosen to implement, thank you SBerthold. I'll probably create a function which converts a 2-dimensional array to an array of arrays, then I can just do the row loop and hand each row entire into the recordset.

 

[SkipVought]

Good one! Thanx!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[ESquared]

For what it's worth, the effort to convert a 2-dimensional array to a 1-dimensional array (or an array of 1-dimensional arrays) ends up making the thing actually run slower. Until VBA provides native Array structure transformation, it's faster to loop through the columns.

Actually, if you know the fields in advance, it's fastest to create a variable for each field and set the variable to the field, then have a line of code for the setting of each field.