October 5, 2009 at 11:25 am
I have a reporting front end in Access, which I need to set (as an interm fix) so only one user can have the file open at any given time. I have attempted the exclusive mode, but that seems to be based on designing of the database, rather than running the reports. Does anyone know if there is a way to prevent two users from opening the file at the same time?
(The real fix will be changes on the SQL server side to the queries, but time constraints will delay this fix for a month or two.)
--One would think if there are only 3 users of the reports and they all sit in the same room within 4' of each other there would be some way they could work this out, for a short while at least. ah well, expectations are a bit high on my part.
October 5, 2009 at 11:48 am
The only methods I can think of would be unstable and untrustworthy.
Any reason to not have multiple Access files and bypass whatever issue you're running into?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 5, 2009 at 12:03 pm
Well, the issue is as this is a front end for roughly 30 standardized reports, which will be all generated with the same criteria set, I have set up a table which holds the criteria the user enters, and all the SQL statements use that table for the parameters.
The issue is when two people run the reports, or keep the Access front end open, it will add a second set of criteria into the criteria table, which then causes all the other SQL to pull data for both sets of criteria. The long term fix will be to either paramaterize all the reports, or to code to add a user id to the criteria table, and have the reports all select only the criteria for the given user.
Unfortunately, I have other much higher priorities to work on at present. I want to be able to at least address this in some fashion right away, hence having only one person in at a time.
Heh, part of the design specs which all involved assured me would be no problem was "only one user will be in this tool at a time, and the three who use the tool will communicate to handle this". One of those times I should have just ignored the specs and followed my instincts.
October 5, 2009 at 12:57 pm
That makes more sense now.
The only thing I can think of is adding a VBA "on startup" script to the Access file which would log that someone is using it. Then it would have to log that they were done. If it found that someone had logged use without logging completion, it wouldn't allow any reports to be run. There are On Startup and On Shutdown events available for the Access object that would allow this.
The danger is that, if the application crashes and doesn't log a shutdown, or if someone leaves it running and goes home or anything like that. Easy enough to handle, but it'll require you going in and faking a logoff in the table.
Does that idea make sense as a stopgap?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 5, 2009 at 1:19 pm
Yes, those do make sense. Definately on a different path of thinking than I was heading down, which is very helpful.
I can work with this thinking. I actually think something with nag screens, and situations which "locks" the use of the reports incorrectly will actually work to my benefit in this. Such will keep the project near the top of the priority list, so resources will be dedicated to fix it. (Wait, resources.. that means "me".. ok, that settles it, I am starting to think "too" business.)
Thanks for the idea!
October 5, 2009 at 1:21 pm
"Starting to think 'too' business" - it happens. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2010 at 4:43 am
how about setting the network permissions to read/execute?
the lack of write means no LDB can be generated and the MDB is opened exclusively without one
January 27, 2010 at 8:27 am
"There are On Startup and On Shutdown events available for the Access object that would allow this."
Can you please point me to where, on Access help, it mentions these two events? I haven't been able to find them or how to use them.
January 27, 2010 at 8:35 am
I may have to play around with the network permissions, that seems workable. (Ofcourse, since I have had valid reasons to not get back to this, the people who use it are "somehow" managing to deal with it, go figure huh? ) haha
January 28, 2010 at 7:50 am
In our shop we have done a lot of Access front ends with ODBC connections to the data. Exclusive access in all versions has always worked without messing with NTFS permissions. The key is the way the front end and the system.mdw are configured. You do need a fairly stable network and use several system.mdw copies if there are many users. Three should not be a problem to use a single system.mdw. Here is what you do.
Serve the Access front end and the system.mdw from a folder or folders on the network. In the system.mdw set up a new group to access your front end only. Remove permissions to your front end database for everyone of the groups, except your new group and admin. Place your three users on the new group and make sure they do not have admin permissions. We always remove admin permissions for users with the exception of administrators (You and any other developers). You can not remove any users from the users group, but if you have removed permissions from the users group to your front end this will not be a problem. In the security permissions tab in your database is where you will set permissions for your new group and admin. remove permissions for every other group. You now should have the front end secured properly for using this method.
Now go to your users desktop and use the workgroup administrator to join them to the network system.mdw. It helps to remove the local system.mdw. In the Access2k or 2k+ databases the rejoining must be done after your front end is open. Create a shortcut to the system served version of your front end. This process will always restrict access if the database is opened exclusively. It makes updates to the front end easier and it restricts access to your front end to just the users you want to have access. This is because of the group permisions you set on the front end - only those users can access it.
We have used this method for years - first learned it id a Access handbook written by Getz and Lithwin on Access97.
Hope this helps. It is just Access permissions and reconfiguring your environment a little.
January 29, 2010 at 3:32 am
David Lester (10/5/2009)
Well, the issue is as this is a front end for roughly 30 standardized reports, which will be all generated with the same criteria set, I have set up a table which holds the criteria the user enters, and all the SQL statements use that table for the parameters.The issue is when two people run the reports, or keep the Access front end open, it will add a second set of criteria into the criteria table, which then causes all the other SQL to pull data for both sets of criteria. The long term fix will be to either paramaterize all the reports, or to code to add a user id to the criteria table, and have the reports all select only the criteria for the given user.
Unfortunately, I have other much higher priorities to work on at present. I want to be able to at least address this in some fashion right away, hence having only one person in at a time.
Heh, part of the design specs which all involved assured me would be no problem was "only one user will be in this tool at a time, and the three who use the tool will communicate to handle this". One of those times I should have just ignored the specs and followed my instincts.
Put the table that holds the parameters in the front end, not as a table in the back end, and give each user their own copy of the front end
January 29, 2010 at 8:21 am
I may have missed something here, but why not just move the criteria table to the front end. (I assume each user is using their own copy of the front end application.)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply