Pass-Through Queries and Multi-User

  • Thread starter Thread starter Larry Stewart
  • Start date Start date
L

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
 
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.
 
Larry Stewart said:
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?
Click to expand...

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).
 
Back
Top