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.