Transferring query records to VBA Array

[John floyd]

Is there a way to transfer the data from a query to a VBA
array fo further processing? I have used DLOOKUP to get a
single record into a variable but, I need to transfer all
records into an array for further processing. I cannnot
figure out how to make the DLOOKUP index down a recordset.
Any help would be greatly appreciated.

 

[Ken Snell]

Something like this (code assumes just one field per record; you can adjust
if you have more fields):

Dim varArray() As Variant, lngNum As Long
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("QueryName")
rst.MoveLast
rst.MoveFirst
lngNum = rst.RecordCount
ReDimi varArray(1 To lngNum)
lngNum = 1
Do While rst.EOF = False
varArray(lngNum) = rst!Fields(0).Value
lngNum = lngNum + 1
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[CSmith]

Hi,

I have used DLOOKUP to get a
single record into a variable but,
I need to transfer all records into
an array for further processing.

Since you need ALL records stored, perhaps opening a querydef recordset on
your original query would be a more elegant idea instead of swimming through
all of your records and populating an array. That way, you have a snapshot
of all your records. Or if you still want an array, then another idea would
be to use a Type statement.

Example:

Private Type MyEmployeeData
lEmployeeNumber As Long
dHireDate As Date
strFName As String
strLName As String
strMI As String * 1
'etc...
End Type

Private AllMyRecords() As MyEmployeeData

[your function here]

....which would loop through your querydef and populate the array elements.

 

[david epsom dot com dot au]

If you need to get your data into an array, then

dim rst as dao.recordset
dim vTmp as variant
....
3070 vTmp = rst.GetRows(nMaxGetRows)
....


But a recordset IS an array, and sometimes you can
use a function with a snapshot rather than an actual
array:
n = fnArray(i,j) * fnArray(i+1,j+1)

function fnArray(i as integer, j as integer) as double
.....

(david)