• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Extract the data from an ADO Recordset into a 2-D variant array

Extract the data from an ADO Recordset into a 2-D variant array

Extract the data from an ADO Recordset into a 2-D variant array

Rate Extract the data from an ADO Recordset into a 2-D variant array

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

    Dim avarStuff() As Variant      ' Hold the returned records
    Dim fldThis As ADODB.Field      ' Iterator for the Fields collection
    Dim iIndex As Integer           ' Loop index
    
    ' Resize the array to hold the column names
    ReDim avarStuff(1 To oRst.Fields.Count, 0 To 0) As Variant
    
    ' Extract all the column names from the Fields collection
    iIndex = 1
    For Each fldThis In oRst.Fields
        avarStuff(iIndex, 0) = fldThis.Name
        iIndex = iIndex + 1
    Next fldThis
    
    ' Move to the first record in the recordset
    oRst.MoveFirst
    
    ' Loop through the records, and build the 2-D array
    Do While Not oRst.EOF
        ReDim Preserve avarStuff(LBound(avarStuff, 1) To UBound(avarStuff, 1), _
            LBound(avarStuff, 2) To (UBound(avarStuff, 2) + 1)) As Variant
            
        For iIndex = LBound(avarStuff, 1) To UBound(avarStuff, 1)
            avarStuff(iIndex, UBound(avarStuff, 2)) = _
                oRst.Fields(avarStuff(iIndex, 0)).Value
        Next iIndex
        
        oRst.MoveNext
    Loop
    
    ' Return the array
    RecordsetToVarArray = avarStuff()
End Function