Best way to count records returned from a query...

[Paladyr]

If I am opening a table, the recordcount property should always be correct right?

If I am opening a query, it will only be accurate if I use a cursor type like dbOpenSnapshot... not dbOpenDynamic correct?

Would it be better if I have an open dbOpenDynamic recordset to do a .MoveLast and then get the recordcount property or setup a new recordset and open it using dbOpenSnapshot? I've actually seen recordsets where I do a .MoveLast and the recordcount is still not correct so perhaps that doesn't even work all the time!

Also, is ADO any different in this respect? Is it easier to get a record count?

I can't use an SQL query with COUNT() in it because these are dynamically built queries and I would have to write code to alter the select statement and that just seems impossible to write a function for.

 

[DangerPowers]

You may need to move to the last record in the recordset with MoveLast in order to get an accurate RecordCount.

 

[Kendel]

You may need to MoveFirst before MoveLast

 

[lovell811]

What happens is that jet handles queries in two stages the first one only opens enough data to enable your program to keep working without a noticeable delay processing, the second one executes in the background, retrieving all the data required to execute the query completely. It handles it like this for performance reasons. As for what you should do. I think you should set it as as a snapshot(which are usually read only) recordset, or use the dbopenforwardonly(which is snapshot style with a forward cursor only). ----------------
Joe

 

[BlackKnight]

Hi,

The recordcount property is usually best to avoid since not all providers support it. Also moving last isn't always best especially if your data volume is large. If you are already creating queries dynamically then including the count() can't be that difficult?

Have a good one!
BK

 

[Paladyr]

Oh wait I think i just got it... duh....:

'I am doing this:
strSQL = "select col1, col2, col3 from table where blah blah"

'When I can just do this:

strSQL = "select COUNT(col1), col1, col2, col3"

Correct?

 

[lovell811]

yeah that looks about right. ----------------
Joe

 

[BlackKnight]

Hi,

Absolutely!!

Have a good one!
BK

 

[Paladyr]

wouldn't i have that count number repeating down the entire recordset?? Is that bad? Is there a better way of doing it using ADO???