Large Record sets + recordcount property

[Geoff Gunson]

HI

I am opening LARGE record sets with 1000's of records in them. I have made a progress bar to indecate how far through the RS the app has read. What I did was to work out the percentage using Current (RS num / RS record count) * 100. What I notice is if the RS only has 500 records or less this works out to be fine, however if the real count is 700 + it seems to set the record count to 500, read those records then re-set the record count to 700. This makes the progress bar scroll up 100% then down to 85% then scroll back upto 100%

Does anyone know what's causing this?

It is because I open the RS using DbOpenDynaset?

Any takers?

Thanks for reading this essay of a question.

Geoff :)

[Ghost]

Record Count is not the best way to determine how many records has been fetched because it relies on the ODBC driver to give that information (which is not accurate all the time). After you have fetched the data, do a move last, check the value of record count. this will give the total number of records fetched. Then, do a move first to reset the pointer and proceed with your processing.

rs= OpenRecordSet(blah, blah)

rs.movelast
numrows = rs.recordcount
rs.movefirst

while not rs.eof
progressbar.value = (currentcount/numrows) * 100
wend

HTH

[Ghost]

Record Count is not the best way to determine how many records has been fetched because it relies on the ODBC driver to give that information (which is not accurate all the time). After you have fetched the data, do a move last, check the value of record count. this will give the total number of records fetched. Then, do a move first to reset the pointer and proceed with your processing.

rs= OpenRecordSet(blah, blah)

rs.movelast
numrows = rs.recordcount
rs.movefirst

while not rs.eof
progressbar.value = (currentcount/numrows) * 100
wend

HTH

[Crazy D]

That words fine, but with larghe recordsets this might take a while.
You can use a 2nd recordset to use a count query: rst.Open "Select Count(*) from mytable"
(or use instead of * the name of a field that is always filled).
rst(0) gives you the result.

[Vit]

I can give you another hint. I have database tables with 100 000 records and more so selecting even one single field from the whole table becomes unacceptable. So think of selecting one record at a time and skipping to next/previous when needed. This will save your resources and increase performance

------------------
Regards,
Vit