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.