Make Recordset into Array

[Kib]

Hello,
I need help converting a recordset into an array. Not sure what the best way to do this is. Thanks.
Kib

 

[JoeMiller]

Why? Access is better suited for working with recordsets, so if you've got the recordset working use it! Arrays are easier to work with in VB, but not VBA. Is there some compelling reason you need to use an array?

Joe Miller
joe.miller@flotech.net

 

[Guest_imported]

Set rst = CurrentDb.OpenRecordset(sSQL)
'Redim array and store headings
With rst
.MoveLast
lngFieldCount = .Fields.Count - 1
lngRecordCount = .RecordCount
ReDim vTable(0 To lngRecordCount, 0 To lngFieldCount)
.MoveFirst
Do Until .EOF
For lngFieldCounter = 0 To lngFieldCount
If lngCurrentRecord = 0 Then
vTable(lngCurrentRecord, lngFieldCounter) = .Fields(lngFieldCounter).Name
Else
vTable(lngCurrentRecord, lngFieldCounter) = .Fields(lngFieldCounter).Value
End If
Next lngFieldCounter
lngCurrentRecord = lngCurrentRecord + 1
If lngCurrentRecord > 1 Then
.MoveNext
End If
Loop
End With

I use this snippet to pack a rst into an array and then export it to Excel thru aUtomation.

With oWS
.Range(.Cells(1, 1), .Cells(lngRecordCount + 1, lngFieldCount + 1)).Value = vTable
.Columns.AutoFit
End With