[Don Sabo]

I have a problem with an Access 2002 database that I wrote. The problem is that I have 5 users that want read/write capabilites while there are 20 users who need read only capabilites and the ability to view reports. I do not want to use Access Security if I can avoid it at all costs. I first had my network engineer map a new drive where the file is stored and the 5 people had read/write privileges. The group of 20 had read only priviliges and if one of the 5 users were on, they got an message that the database was locked. I then tried to split the database into a front end and a back end according to article 304932 from Microsoft support. I also tried to copy the tables using a macro when the user was logging off, but I could not because I have joins with 3 tables. I also tried opening the file as Read only, but I know the users will not remember to do this every time they log on. Does anyone have any suggestions? If your suggestion is too complicated send me a PM and I will call you to discuss it. Thank you.

[andrea.bbbs]

Here is a possible idea. Create a form in your database to view the data. Create a macro called Autoexec, and the only thing that macro should do is open that form and maximize it. That means that whoever opens up that database will automatically open that read-only form.

Next, tell your users with rights to change the database to hold down the shift key when they open the database. This will bypass the autoexec. The people who should olny be view read-only probably don't want to cause any trouble, so even if they figure out the shift bypass they won't use it.

It's a shot.

[mwittman]

Andrea's idea sounds pretty good.

I'll throw in another approach that may work for your requirements:

For all the read-only users, you could replace the shortcut to the Access application itself with a shortcut to a command script that copies the "master" .mdb file to a local copy, then opens the local copy to the user.

I sometimes use this technique for .NET Windows applications hosted on a network share since you can't run those things directly from the share (due to a security restriction imposed by the .NET framework).

Here's a Windows command script example:


FILE NAME: MyAccessApp.cmd

SCRIPT:
----------------------------------------------
REM "S:\" would be mapped to the share hosting the master Access database application

REM Copy master file from network share to client local hard drive
xcopy /y /s /i "S:\MyAccessApp.mdb" "%APPDATA%\SteelersRule\MyAccessApp\"

REM Open local copy of application
"%APPDATA%\SteelersRule\MyAccessApp\MyAccessApp.mdb"
----------------------------------------------


I feel your pain on this issue - I've dealt with it and still deal with it


Micah

[Simon Stacey]

I had to resolve something similar in a previous role - but I went for the split database approach, with two different front ends - on one of them the forms were locked down so that the data was read only, while the "admin" version allowed edits. Another database used a pop-up with a very simple "password" style field to unlock the admin screens.