.recordcount vs. .eof

[_kyle_]

Using VB, what is the best way to cycle through a recordset?

for x = 1 to rs.recordcount

next x

OR

while not rs.eof

wend

I've heard that .recordcount can be inconsistent at times; however, I don't know if that is true or not. It would seem that .recordcount would be more efficient than .eof because you don't have to check and see if the recordset is "end of file" yet.

[HunterMcCray]

Originally posted by _kyle_
Using VB, what is the best way to cycle through a recordset?

for x = 1 to rs.recordcount

next x

OR

while not rs.eof

wend

I've heard that .recordcount can be inconsistent at times; however, I don't know if that is true or not. It would seem that .recordcount would be more efficient than .eof because you don't have to check and see if the recordset is "end of file" yet.

If you are going to use .recordcount then you need to use the .movelast command first and then the .movefirst command. From an efficiency point-of-view I have never noticed any difference between a do-loop and a for-next loop. The .MoveNext action is a mask for a lot of overhead, and the comparison of .EOF vs the implied addition and comparison of the counter variable is small in comparison. Using the .MoveLast followed by the .MoveFirst command causes the datadase to cycle through all of the records. If you have a very large database (100,000 records or more) you might go to the trouble of running some time trials, but make sure that you include the .movelast and .movefirst overhead in the trials for a true test.

Hope it helps,

Hunter

[honeybee]

You seem to be quite well-informed!

Yes, .RecordCount behaves differently with ADO, DAO and RDO.

In DAO the .RecordCount returns the number of records accessed. If you have 30 records in your recordset, unless you access all 30 records, the .RecordCount property won't show you the correct number. That's why you have to use .MoveLast before you can get the correct count.

In ADO the .RecordCount works as it is supposed to be, it returns the number of records in the recordset irrespective of whether you have accessed them or not.

In RDO which is now a thing of the past, .RecordCount returns -1 if there are records. So again you have to access the records to find out how many are actually there.