Results 1 to 4 of 4

Thread: Populating an ADODB.Recordset from array without looping ?

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2013
    Posts
    634

    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.

  2. #2
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,422

    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>

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,956

    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.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2013
    Posts
    634

    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
  •  



Click Here to Expand Forum to Full Width