How to Get an ADO RecordSet into an Array.

Introduction

Often it is required to get the records retrieved in an SQL fetch into a recordset to an array. This is made possible using the GetRows method of the Recordset Object which inturn returns a variant array.

The procedure is explained below

Dim dbA As ADODB.Connection

Dim rsA As ADODB.Recordset

Set dbA = New ADODB.Connection

Set rsA = New ADODB.Recordset

dbA.Open "DSN=tstDSN;UID=sa;PWD=pass;"

Dim csSql As String

csSql = "SELECT * From EmployeeTable”

Set rsA = dbA.Execute(csSql)

If rsA.EOF = True And rsA.BOF = True Then

GoTo exithandler

End If

'Get this to Array.

Dim nNoOfReords as Integer

arrRecordArray = rsA.GetRows

nNoOfReords= UBound(vRecordArray, 2)+1

‘Now arrRecordArray contains the recordset in array format.

Consider the employee table with fileds empID, empName, empAge such that

empID empName empAge

----------------------------------------------

1 Smith 24

2 Baiju 26

3 Ragi 26

4 Pramod 23

5 Bincy 25

----------------------------------------------

Then the variant array structure will be

arrRecordArray(0,0)=1, arrRecordArray(1,0)=Smith, arrRecordArray(2,0)=24

arrRecordArray(0,1)=2, arrRecordArray(1,1)=Baiju, arrRecordArray(2,1)=26

i.e. the first argument specifies the FieldIndex and the second one specifies the RecordIndex.

The number of records returned may be found as

nNoOfReords= UBound(vRecordArray, 2)+1

hope this piece of information will be useful for you.

By Smith.S.Raj

Software Engineer

M-Squared, Technopark.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here