i understand DCOUNT is very slow - especially since you say it's
recalculated every record change (more frequent than i need)
Actually, to clarify what is being said:
dcount() does NOT perform any faster, or slower then the use of a query (ie:
some code, and a query to create a recordset, and then grab the value).
dcount() is revaluated every record, but only when you use it as such. So,
if you open to one form that displays one record, then dcount() does not
just start re-calculation blindly here. However, the point mentioned that it
is so EASY TO use dcount() in all kinds of places, and that is WHEN it hurts
performance. So, it is not the fact of it being slower, or faster then you
using a query, but WHERE and HOW you use it.
So, if you have a continues form, and place dcount() in the sql query, or
even bound as a expression to a text box, and that form displays 20 records
in continues mode, then you fire off that expensive dcount() function 20
times.
For the most part, you as the developer has control over this, so, the
suggestion here is that dcount() vs a query is not slower, but you tend to
hang yourself by using dcount() because it can be used a expression in all
kinds of places with such great ease. It is the "ease" of use that hurts
performance....not the fact that dcount() runs slower then building a query
to do the same. (as mentioned, they run the same speed).
If you removed that dcount() from the continues form, and replaced it with a
function that calls your code that executes the query for each record, you
would find this is just as slow (in fact, I would bet it would be *slower*
then dcount() ).
You can usually eliminate the dcount() in that continues form by using a
sub-query in the original sql. This will run MUCH faster then dcount() or
your code since the query only needs to be "setup" and compiled once....
