Recordset to array?

[Jaymond Flurrie]

I'm using Excel 2010. I know how to put recordset into a range. I would need to put recordset into an array. This is the code I use to put it into a range:

    Set rsData = New ADODB.Recordset
    rsData.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

    If Not rsData.EOF Then
        Range("A1").CopyFromRecordset rsData
    Else
        MsgBox "No records returned.", vbCritical
    End If

And it works, returns 14 results (14 rows, 1 column). The difference should be on the last line, but how?

vSRA = rsData.GetRows(14) 'vSRA is a variant
Range("A1") = vSRA(1, 1)

gives subscript out of range, so does

vSRA = rsData.GetRows
Range("A1") = vSRA(1)

What's wrong? Do I need some to alter something else than that last row (where I copy it from the recordset to a range)?

 

[Jaymond Flurrie]

Also, when I want to query again, should I do

    Set rsData = Nothing
    Set rsData = New ADODB.Recordset

or is that first line needless?

 

[ukmikeb]

Hi

To load the results of your query into an array use :-

vSRA = rsData.GetRows()

then to find the number of records that you have retrieved -

returnedRows = UBound(vSRA, 2)

then filling the cells -

Range("A1") = vSRA(0, 0)

The records are stored starting at zero.

If you are retrieving a different set of records it is always good housekeeping to set the recordset to "Nothing".

hth

Mike

 

[Jaymond Flurrie]

Thanks, works nicely!