-
Oct 15th, 2021, 01:55 AM
#1
Thread Starter
Fanatic Member
Populating an ADODB.Recordset from array without looping ?
Hi,
The following ADOCopyArrayIntoRecordset function copies each array element to the recordset one at a time inside a For\Next loop.
Does the ADODB library offer a way to populate the recordset without having to loop through each individual element of the array ?
I am looking for some faster method for copying large arrays.
Code:
Option Explicit
Sub Test()
Dim vCountriesArray(3) As Variant
Dim oADO As ADODB.Recordset
vCountriesArray(0) = "UK"
vCountriesArray(1) = "BRASIL"
vCountriesArray(2) = "SWEDEN"
vCountriesArray(3) = "ITALY"
Set oADO = ADOCopyArrayIntoRecordset(vArr:=vCountriesArray)
With oADO
.MoveFirst
While Not .EOF
MsgBox .Fields(0).Value
.MoveNext
Wend
End With
MsgBox "Done"
End Sub
Private Function ADOCopyArrayIntoRecordset(ByVal vArr As Variant) As ADODB.Recordset
Dim oADO As ADODB.Recordset
Dim i As Long
Set oADO = New ADODB.Recordset
With oADO
.Fields.Append "Country", adBSTR
.Open
For i = 0 To UBound(vArr)
.AddNew
.Fields(0).Value = vArr(i)
.MoveNext
Next i
.MoveFirst
End With
Set ADOCopyArrayIntoRecordset = oADO
End Function
I have never used the ADO library before so I am still learning.
Regards.
-
Oct 15th, 2021, 06:11 AM
#2
Re: Populating an ADODB.Recordset from array without looping ?
There is GetRows method on ADODB.Recordset to "read" data into a 2D array but there is no equivalent SetRows method -- you have to loop rows and fields on your own.
Also, don't use adBSTR with synthetic recordsets as this makes them unpersistable -- i.e. you cannot write these into a PropertyBag for instance.
Just use adVarWChar with size of 1000 -- the size rarely matters and assigning longer strings will not fail (though you might get truncation in some rare cases).
cheers,
</wqw>
-
Oct 15th, 2021, 06:27 AM
#3
Re: Populating an ADODB.Recordset from array without looping ?
Yeah, this doesn't exist because there wasn't any need for it. Very early Microsoft considered a "PutRows()" method and dropped it because there weren't any use cases for it.
Create the data as a Recordset in the first place. Then in rare cases where you want an array to buzz over repeatedly you always have the GetRows() method. Recordset is a first class datastore, feature-impoverished simple arrays are second class.
-
Oct 16th, 2021, 11:25 AM
#4
Thread Starter
Fanatic Member
Re: Populating an ADODB.Recordset from array without looping ?
Thanks for the clarification.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|