Recordsets vs Collections vs Arrays

[frigginjerk]

I've been using collections for quick lookups...
it'll be easier if I show you through code instead of explaining too much.

I do:
option explicit
dim db as dao.database
dim rs as dao.recordset
dim colIds as Collection
dim fldTemp as Dao.Field

Private Sub Something
set colIds = New Collection
set rs = db.querydefs("qryName").openrecordset(dbopenforwardonly)
if rs.eof = true then
else
set fldtemp = rs.fields("fldName")
do until rs.eof = true
colIds.additem fldTemp.value
rs.movenext
loop
rs.close
set fldtemp = nothing
endif
set rs = nothing
end sub

The field is of LongInteger datatype. I use the collection to query other records by their ID in the table. This works pretty well for me, but this is only good if the collection stays small because collections use variants.

So I was wondering if making another recordset object that is not attached to a database, with one LongInteger field would be a more viable than using a collection?

Also what if the field in tha database was Date and not a LongInteger, Dates take a lot more space (I think). Should I just stick with a collection then?

I thought about using a one dimensial Array but I dont know enough about them.

Any thoughts on what would be faster, and which will take less space in memory?

[frigginjerk]

hope nobody minds that i bump this

[Edneeis]

Since are already went to the database and retrieved all the records (at least for that field) then it'd be easier to just use the filter method. I'm not sure if DAO has the filter method I think it does though. SIDE NOTE: DAO is of the devil, by-the-way. The filter method applies a sort of sub select query to the contents of a recordset.

rs.Filter="fldName=" & fldValue
'now the recordset is filtered to contain only the records that match the criteria.
 
'to remove the filter
rs.Filter=""
'now it has all the contents again

You can also use Like or whatever you need to deal with other datatypes. Also you would set the connection to nothing to disconnect the recordset in ADO.

What is it that you need to quick look up? Why is this list needed?