Transferring query records to VBA Array

  • Thread starter Thread starter John floyd
  • Start date Start date
J

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.
 
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
 
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.
Click to expand...

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.
 
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)
 
Back
Top