OK, so here is an example of using a dynamic array.
Code:
Public Sub MakeArray()
'uses DAO
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varArray() As String 'dynamic array name
Dim k As Integer 'counter
Dim RC As Integer 'record count
Set dbs = CurrentDb()
k = 0
'open recordset
'SQL of query = "SELECT DISTINCT fruit_n_veg_shop.Fruits FROM fruit_n_veg_shop;"
'NOTE: query has only one field
Set rst = dbs.OpenRecordset("qryFruits", dbOpenDynaset)
If Not (rst.BOF And rst.EOF) Then
rst.MoveLast
RC = rst.RecordCount
rst.MoveFirst
'set array dimension
ReDim varArray(RC)
'fill recordset
Do Until rst.EOF
varArray(k) = rst.Fields(0)
rst.MoveNext
k = k + 1
Loop
End If
'--------------------------------------
' do other stuff here with the array
'--------------------------------------
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub