Unanswered: multiple instances of same query in multiuser db?
When coding in vbe, I am sometimes tempted to only reference an existing query by name, instead of including the SQL in the sub/function. (so far I have always included the code)
In a multiuser db, running union queries and update queries etc. from perhaps many workstations/users at the same time, is it "safe" to refer by name? Could using the same query at the same time for two different workstations/users cause trouble that would be avoided if not calling a stored query from the sub/function but rather including the code?
About the situation:
It is not the case that the same update will take place, but I use several queries as subqueries for misc. others, so it is really the situation where one user runs an update query that uses the same subquery as another user - but the latter uses it either directly/behind a form or as a part of a select query, not an update query. Could be that the latter user is running a different update query (on other tables), and only using the subquery as a lookup.
So, are there situations where a conflict could cause (parts of?) an update query to fail, if referencing the query rather than including the code in the sub/function? What is "safest"?
I don't have much experience in what you said, but can I suggest you use a client/server system (single mdb on server holds all tables with changing data, each computer has an mdb/mde that holds all forms, queries and reports with tables linked). That way, each user would have a seperate copy of the queries and forms on their computer avoiding any of the conflicts you suggested. Additionally, make the queries snapshots where possible to avoid access locking records unnecessarily.