Count: -- Best Practices

[grndpa]

I've found three approaches to obtaining a count of the rows that satisfy my Microsoft 2003 jetSQL.

One is to use the COUNT(*) in a standalone SQL statement using the same FROM, JOIN and WHERE criteria as the separate SQL statement that actually gets the data.

Another suggests changing the SQL open from "forward only" as to allow the .MoveLast imperative to work, followed by the .RecordCount.

A third suggests loading the dynaset into an array and using the UBOUND.

The only reason I'm using "Count" at all is to instantiate and maintain a progress bar.

What do you recommend. And what are the tradeoffs?

Regards,

Grandpa Brian

 

[jrbarnett]

I would recommend using the first one - it should (theoretically) be the fastest, as all the data access is done without using VBA and you are just reading the total number of records.

Both the other options involve opening a recordset object or loading all the data from the table into memory, which could take a while if there's a lot of it. Presumably the reason for the progress bar is because there is a lot of data and you want to give the application users some sort of idea how far it has got through its processing.

John

 

[grndpa]

Thanks jrbarnett.

Thanks for the confirmation that I stumbled into the more appropriate solution.