Array To ADO Recordset

Vishesh's picture
This is helpful in handling excel data in the same way as access data. You can easily work on recordset filters using this tip.
Function rstArrayToRecordset(arrField As Variant, arrData As Variant) As ADODB.Recordset
    Dim rstData             As ADODB.Recordset
    Dim recordString        As String
    Dim arrRecord           As Variant
    Dim intLoop1            As Integer
    Dim intLoop2            As Integer
    ReDim arrRecord(1 To 1, 1 To UBound(arrData, 2))
    Set rstData = New ADODB.Recordset
    For intLoop1 = 1 To UBound(arrField, 2)
        rstData.Fields.Append arrField(1, intLoop1), adVarChar, 500
    Next intLoop1
    For intLoop1 = 1 To UBound(arrData, 1)
        For intLoop2 = 1 To UBound(arrData, 2)
            arrRecord(1, intLoop2) = arrData(intLoop1, intLoop2)
        Next intLoop2
        rstData.AddNew 'arrField, arrRecord

        rstData.Fields(0).Value = arrRecord(1, 1)
        rstData.Fields(1).Value = arrRecord(1, 2)
        rstData.Fields(2).Value = arrRecord(1, 3)
        rstData.Fields(3).Value = arrRecord(1, 4)
    Next intLoop1
    Set rstArrayToRecordset = rstData
    Erase arrRecord
    Set rstData = Nothing
End Function

Array to ADO Recordset

ArrayToRecordset.xls41.5 KB