Making a querydef suitable for multi-users

[Plateriot]

Isn't there a way to setup a querydef so that multiple users don't interfere
with each other?

This is how I currently am doing it:

strQryPivotBase = "SELECT * FROM qScoreCardUnion;"

'I know this saves it in the MDB which is not multi user
Set qdf = db.QueryDefs("qScoreCard")

qdf.Sql = strQryPivotBase
db.QueryDefs.Refresh

 

[Douglas J. Steele]

Given that all applications should be split into a front-end (containing the
queries, forms, reports, macros and modules), linked to a back-end
(containing the tables and relations) and that each user should have his/her
own copy of the front-end (ideally on his/her hard drive), you shouldn't
have any problems with what you're doing. What problem are you experiencing?

 

[Plateriot]

Now that you've asked that question I can say that - Excel is my front-end
and I realized that the error - was that the qScorecard QueryDef existed on
the actual MDB.

So the correction I made was to define a query on the Excel Side, including
the parameters that it handles.

This should allow each user to avoid clobbering each other correct?

Or do I need to turn on a special DAO flag to allow the query to function in
multiple threads?

 

[Douglas J. Steele]

Queries should be able to function in multiple threads with no flag, but
it'll be the same query everywhere.

Doing everything in Excel should solve the problem.

 

[Rob Wills]

Or alternatively you can add a string to the querydef which is user specific

try naming the querydef using:
environ("Username")

on error resume next
set qdf = db.querydefs("qscorecard_" & environ("username")
if err.number <> 0 then
set qdf = db.CreateQueryDef("qscorecard_" &
environ("username"),db.querydefs("qscorecard").SQL
end if
on error goto 0