I try to avoid MSAccess security. Instead, I'll use a permissions system such as demonstrated here: http://www.dbforums.com/6332819-post68.html
Unless you have user's who are developing (and need to allow some users permissions to edit the design of forms), security is more easily controlled through vba code using the getUser code to automatically grab the user's loginID. Since user's shouldn't ever be editing data directly into the tables, you then control their permissions to edit the data in the specific forms themselves as the example portrays. This gives you field level control of permissions as well as table level control.
The reason I don't like MSAccess security is that mdw files sometimes become corrupt or get lost. Once that happens, without a backup of the mdw, you've essentially completely locked your application! (rarily can you recover/edit an mdb file without an mdw file if MSAccess security has been implemented.) I would rather control security to the tables (via SQL Server), and the permissions to edit the data in the tables through the frontend forms (matching permissions against a user type table as the example illustrates.) If SQL Server is not an option, again, control of data entry permissions still takes place via the forms. When needed, you basically lookup a user's 'permission level' in a user type table. You have a function which returns that user's permission level and in the form's onLoad or OnOpen event, you simply set the ability to edit fields/records based on what the function returned. The nice thing about this is that you could (in the example), have code which automatically quits the mdb if a user is not in the user type table. You can also let the user's themselves easily assign other user's permission levels or add them to the user type table (to give other users permissions to use the mdb.) It eliminates the need for someone having to always come to you should they want to add a new user or change someone's permission level. Also, often permissions need to happen on a FIELD level versus a table level which you can easily implement using this method.
I simply copy/paste my function/user table/user forms from one mdb to my new one. Then I add the few lines of code it takes to lock/unlock certain fields/buttons on the data entry/menu forms. I also use the 'background' and popup/modal form method as shown in the example to prevent user's from closing to get behind the scenes to the tables (note: in 2007, you have to turn off the ability to see the left side objects.) This method still allows the use of holding down the shift key while opening the mdb in order to get behind the scenes for coding. Since typical user's don't know this trick, it keeps them out of the backend. (even if they did start entering data behind the scenes, I'd eventually discover it.) I also use compiled versions of the mdb (or accdb). An mde or accde (for the frontend) doesn't allow users to see any code and runs more efficiently since it's compiled.
I use this system quite often so I just wanted to let you know another way.
Also, there have already been several posts on this forum where someone has posted a corrupt mdb file for help (without the lost mdw file) and there's essentially nothing that can be done to help them other than to recommend restore backups since MSAccess security was implemented.
Restoring backups is a last resort thing for me since backups for our network only happen on Sundays.
Last edited by pkstormy; 10-11-09 at 15:28.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)