Pass-Through Queries and Multi-User

[Larry Stewart]

I am using Access 2000 mdb and SQL Server 2000. I have a
report that uses a pass-through query to SQL Server. The
Access database is not split and is stored on a network
drive. (At this time I do not wish to use ADP).

I need to change the report parameters based on the
user's input and I'm doing this by modifying the pass-
through query in VBA code with the querydef object.

My question is this. In a concurrent user environment,
will modifying the query via VBA code cause any issues if
the report is run at the same time by 2 different users
with 2 different parameters?

Can I be guaranteed that each user will get the correct
report values based on their input?

Thanks,

Larry

 

[Duane Hookom]

If you change the SQL property of a querydef in a shared MDB, it will affect
all users. This is one of the reasons why it is preferrable to provide a
front end to each user.

 

[Rick Brandt]

I am using Access 2000 mdb and SQL Server 2000. I have a
report that uses a pass-through query to SQL Server. The
Access database is not split and is stored on a network
drive. (At this time I do not wish to use ADP).

I need to change the report parameters based on the
user's input and I'm doing this by modifying the pass-
through query in VBA code with the querydef object.

My question is this. In a concurrent user environment,
will modifying the query via VBA code cause any issues if
the report is run at the same time by 2 different users
with 2 different parameters?

Can I be guaranteed that each user will get the correct
report values based on their input?

You will definitely have problems. The qrydef (and its SQL) do not reside in
client memory so if two people run the report at the same time they will be
stepping on each other regarding the query modifications. You *really* need to
split the app (for a lot more reasons than this).