Currently creating an MS Access database that will reside on a shared drive, and looking for a simple solution that will control access to 2 different forms based on the userid. I could use the user's windows login or create a unique userid and password that is managed by the database. I'm looking for something that's simple and similar to the user level access in earlier versions of MS Access. I've looked a bit in the forum, but having found anything explicit that would work. Any suggestions?
Question User Level Access in 2007 - Simple Solution
- Thread starter duthiedon
- Start date
- Local time
- Yesterday, 22:18
- Joined
- Feb 28, 2001
- Messages
- 30,566
Ac2007 doesn't include user-level security, so either you have to do something really ugly in windows or you have to do a small amount of VBA work.
1. You can get a login name from the Environment ("USERNAME") function. If you have a managed domain, this would be your user's login name and should be both unique AND VERIFIED (by the domain)
2. You can have some little table that lists usernames and levels of access. There are SO many ways to skin this cat that I can't begin to tell you what you need. Only you know the granularity you need.
3. Whatever you do, you CANNOT allow the users to see the navigation pane. You will need a front-end form that is your "default form" that comes up on DB launch. The only way out of this form MUST be to exit it - unless you are the person whose access level allows closing the front-end form.
4. When your default form opens (i.e. the Form_Open(Cancel as integer) event entry point), test your environmental variable to get the username so you can decide whether the person has full access, restricted access (and what kind of restrictions), or NO access. If you decide "no access" then in the Form_Open routine, set Cancel to -1 so that you cancel the form's open event. That stops the form from opening.
5. Typically, this default form has something clickable to launch reports or other forms. Behind the click events for each, you can test the user's role that you looked up in the Form_Open routine. If the user should not get into the form underlying the button they just click, pop up a picture of a raspberry or something.
It sounds complex but it really isn't so bad. Each event code segment would be fairly short. It is more tedious than difficult. For the system I built that way, I used a button wizard to build the code to launch a form, then just modified it to test the user role BEFORE launching the implied form. All you do is branch directly to the Click-event exit label if the person should not be opening that form. Might be as simple as a single If/Then/End If situation.
6. Fancy-scmancy touches would include popping up message boxes with the vbOKOnly format with a snarky little warning message like "you know you aren't supposed to be clicking that button, don't you?"
1. You can get a login name from the Environment ("USERNAME") function. If you have a managed domain, this would be your user's login name and should be both unique AND VERIFIED (by the domain)
2. You can have some little table that lists usernames and levels of access. There are SO many ways to skin this cat that I can't begin to tell you what you need. Only you know the granularity you need.
3. Whatever you do, you CANNOT allow the users to see the navigation pane. You will need a front-end form that is your "default form" that comes up on DB launch. The only way out of this form MUST be to exit it - unless you are the person whose access level allows closing the front-end form.
4. When your default form opens (i.e. the Form_Open(Cancel as integer) event entry point), test your environmental variable to get the username so you can decide whether the person has full access, restricted access (and what kind of restrictions), or NO access. If you decide "no access" then in the Form_Open routine, set Cancel to -1 so that you cancel the form's open event. That stops the form from opening.
5. Typically, this default form has something clickable to launch reports or other forms. Behind the click events for each, you can test the user's role that you looked up in the Form_Open routine. If the user should not get into the form underlying the button they just click, pop up a picture of a raspberry or something.
It sounds complex but it really isn't so bad. Each event code segment would be fairly short. It is more tedious than difficult. For the system I built that way, I used a button wizard to build the code to launch a form, then just modified it to test the user role BEFORE launching the implied form. All you do is branch directly to the Click-event exit label if the person should not be opening that form. Might be as simple as a single If/Then/End If situation.
6. Fancy-scmancy touches would include popping up message boxes with the vbOKOnly format with a snarky little warning message like "you know you aren't supposed to be clicking that button, don't you?"
Alansidman
AWF VIP
- Local time
- Yesterday, 22:18
- Joined
- Jul 31, 2008
- Messages
- 1,493
Here is a link to a tutorial on the permission system I use. While this was set up in earlier versions of Access, it easily converts to AC 2007. Good Luck
http://www.mrexcel.com/forum/showthread.php?t=248191
Alan
http://www.mrexcel.com/forum/showthread.php?t=248191
Alan
Thanks everyone, I ended up using a solution I found at http://www.utteraccess.com/forum/Logon-form-Custom-Securi-t1917534.html (caution, read through the list and get the latest zip file, I learned the hard way 
Similar threads
