ForwardOnly & RecordCount

[gknierim]

Ok, I need some opinions/help on this one so I can file it away in my memory bank. I like to use the recordcount property but I always hear that you should never use recordcount. I personally like it and it has never given me any problems.

My situation: I would like to use the OpenFowardOnly method of opening my recordset but if I do I cannot use the RecordCount property. I have to change it to OpenStatic. Does anyone see this as being a problem? Is there a way I can tell the number of records retrieved using the OpenFowardOnly, (without looping through all of them just to get the number)?? Any help or tips would be great!

Thanks for any suggestions or info.
Greg

[Iouri]

you can get record count by executing an sql. that means you will open rs twice.Once to get recordcount and the second time as ForwardOnly


sSQL = "select count(*) as A from your Table"

rs.Open sSQL, COnn, adOpenDynamic

lRecordCount = rs!A

rs.Close

'now lRecordCount contains number of records in your recordset

'now open recordset to do something

sSQL = "select * from YourTable"
rs.Open sSQL,Conn, adForwardOnly
Do While not rs.EOF
lCounter = lCounter +1
Label1.Caption = "Record " & lCounter & " of " & lRecordCount
Label1.Refresh
'do some other stuff
Loop

rs.Close
set rs = Nothing

Iouri Boutchkine
iouri@hotsheet.com

[gknierim]

Yea, I figured I could do that but the only problem with that is if the database is being updated, the count you received on the first open, may NOT be the same as the second time you access the database. yes, its highly unlikely that it will never happen because we are talking about milliseconds. I may just do it anyway seeing as the table I am going to be using this for won't be updated very frequently.

Thanks for your input.

[dfwade]

if you open as forward only and then loop through to get the record count you will have to reopen the recordset because a forward only recordset does not support scrolling backwards. You can cheat. Use getrows to fetch all the rows and then get the number of rows from the upper bounds of the getrow array.



Yahoo Messenger ID dfWade10900

[Iouri]

rate it if it helped you

Iouri Boutchkine
iouri@hotsheet.com