Your best bet for tables with a lot of records is to store calculated values whenever possible. This can sometimes only be done by using an auto-entered calculation if your calculation references a summary field or field from a related table.
Here's a way to trigger a stored ExecuteSQL expression to update:
ExecuteSQL ("SELECT .... " ; "" ; "" ; YourTable::TriggerField )
If you change the value of YourTable::TriggerField, even if you set it to itself in a set field step, it will case the query to re-evaluate. If you already use an optional parameter in the query, you won't need to add another as you can then use it to trigger re-evaluation.
Auto-enter calculations, if the "do not replace existing value..." check box is cleared will re-evaluate only if a field in the same record that is referenced by your calculation is modified. Changes in values of other records (such as in a field that affects the value of a summary field) will not cause an update. So the same "trigger" trick is sometimes used, sometimes in this manner:
Let ( Trigger = TriggerField ; Calculation Goes here )
It can also be possible to set up a "transactional update" method where you just have simple data fields in your table, but have scripts--often performed by script triggers that calculate a new value and update the appropriate field or fields each time the user changes a value in a field that then requires such a recalculation. This last method can result in pretty fast data, but also requires very meticulous interface design in order to prevent any "loopholes" where the user is able to modify data and not get the required update.
I've also found recently, in a 3 million + table of records, that turning off indexing on a frequently modified field (that did not require indexing) resulted in a small but significant improvement in performance.
This forum software is bugged! I'll try replying for a third time!!
Thanks for the refresh tips, I've got a couple of areas they'll come in useful.
This link above looks good as well to serve as a replacement for a select distinct count function I have which recently killed my DB!