Problem looping through a recordset 1

[PJ]

One more problem - sorry !

Using MS Visual Basic 6.0, the application I'm working on accesses a 21 Mb .mdb, with @250,000 records, file on a file server across a network. The result of a search normally returns about a dozen records to the recordset.

Opening the recordset with a search usually takes less than 10 seconds, and looping through the majority of records in the recordset, to fill a list box, takes a fraction of a second.

My problem is that retrieving the very last record in the recordset takes around 25 seconds, making the total about 25 seconds (too long for me!). The loop is of the form -
While not recordset.eof
.....
recordset.MoveNext
......
Wend.

When the recordset tries to MoveNext and hits the EOF, execution takes a long time (as observed by tracing though).
I've tried finding the matching number of records in the set by query, and reading an ADO static recordset's RecordCount property, but both of these reduce the total time by no more than 10 seconds.

Is there any way to avoid this EOF trap in using a recordset loop ?

Thanks in advance for your help.

Peter Jenkin

 

[swilliams]

The only way around this I can think of is to use the count of records and loop through from 1 to this number (set a variable and use that in a For / Next statement).

However, to get an accurate RecordCount, you will have to do one of two things:
1. Do a MoveLast then MoveFirst - having obvious overheads.
2. Create another recordset, based on the SQL Statement SELECT Count(*) AS Records FROM table_name and store this in the variable to be used in the For / Next statement - again with overheads, but probably more efficient than option 1.

Compared to your current method, I don't know if this would improve performance, but if you do test this, let me know how it goes.

Simon