I totally understand your wish to get to the bottom of this!
There are a lot of factors that can be contributing to the problem
- the query itself, the number of users hitting the query, and the
tmp files internal to Access.
Make sure your query isn't returning very large recordsets
(e.g. all sales for all salespersons for all year.....). Even a
query with a moderate recordset return , hit by a large number of
users, can cause this sort of problem.
If you must use Access in production (which is NOT
recommended) your datasource settings should include:
Maintain connections across client requests be unchecked.
Limit Connections be checked and Restrict connections to be
set to 1
These settings should help prevent performance issues.