load a collection from database without for..next

[SkieGig]

does anyone know how to load a collection from a database (encapsulation) without using the
for each ffield in rrecordset
.
.
.
next ffield
or for i = 1 to rrecordset.count ... next i?
i'm dealing with a huge database, and this just takes TOO LONG.
ideas?

skiegig

------------------
Remembering games and daisy chains and laughs, got to keep the loonies on the path....

[MartinLiss]

If you post your code then someone may be able to help you speed it up.

------------------
Marty

[SkieGig]

Okay, here's the way I do it now. This opens the recordset and loads each record one by one using the recordcount property...

Public Sub Retrieve(cnxConnection As ADODB.Connection)
Dim rstGeneric As New ADODB.Recordset
'load data from database
rstGeneric.Open mConnect, cnxConnection, adOpenStatic
For i = 1 To rstGeneric.RecordCount
Add rstGeneric!F1, rstGeneric!LocLevel1 & " " & rstGeneric!LocLevel2 & " " & _
rstGeneric!LocLevel3 & " " & rstGeneric!LocLevel4 & " " & rstGeneric!LocLevel5, CStr(i)
rstGeneric.MoveNext
Next i
rstGeneric.Close
Set rstGeneric = Nothing
End Sub

any help would be appreciated

------------------
Remembering games and daisy chains and laughs, got to keep the loonies on the path....

[Clunietp]

you could use the .GetRows method of the ADO Recordset. That will put all data into a 2 dimensional array, which would probably be faster then accessing the properties of a recordset. If you try this, let us know if it is faster, thanks

Tom

[Gerald]

SkieGig,

Tom brings up an excellent point. Have a look at the web link below to see some comparative benchmarks. I've conducted my own tests and GetRows is faster. I use it all the time to load global arrays for listbox and combobox data.
http://msdn.microsoft.com/library/periodic/period99/06ivb/insidevisualbasic-usingtheadogetrowsmethodforfasterdataretrieval.htm

Gerald

[Clunietp]

Wow! Thanks Gerald. I guess my assumption proves correct :)