Dynamic WHERE clause with multiple users

[jwhjr]

I need help resolving an "issue", and I haven't had any luck searching. I have a form that is used to run a report, and the criteria for the queries is built in the form. The where clause can be a combination of a number of options, so I have the where clauses being dynamically built in VBA to accomplish this. This works perfectly, but where my problem comes in is that in production this report can be run by multiple users at once. I am altering the querydefs, so it appears that Access can't handle this more than one user at a time. Is there a way to make this work with multiple users? Otherwise, is there a way to notify a user that a particular report or query is being run so they don't attempt to run it at the same time as another user? Any help is appreciated.

JR

 

[jadams0173]

A possible work around would be to make a temporary table and for each user who runs the query and base your form/report off of this unique table for each user. This is not a prefered method as it will cause the db to grow. I've had to do this before and I setup a function that would delete the users temp table when they closed the form/report or exited the db. Don't forget to compact the DB also. Again, this probably isn't the best solution but it should work.

 

[pedrox]

One way you could get round this is by splitting your database into two: tables held in a back-end database; forms, queries and reports etc in the front-end database.
This way the front-end can be small enough for it to be practical for users to have their own copy of it (either on their local PCs, or on their home network drive).
A simple way to have users automatically get the latest version of the front-end is to have them open it via a batch file which uses XCOPY with the switches /D /e /V and /Y, to copy the latest version if it has changed.

 

[jwhjr]

jadams/pedrox - Thanks for the suggestions. The db is currently split front end/back end, but I was hoping to avoid local copies because of the number of users we have (50+). I have also considered the temp table option, but the db size bloat I was also hoping to avoid that option as well. It becomes difficult to perform the compact because there is activity in the db almost 24x7. I may have to lean toward the local copy solution though. Does anyone have any advice about whether or not you can determine if a query or report is currently being used so I could notify the user?