For future reference:
Almost everything on Access can be learned by trial and error EXCEPT SECURITY. Do not enter this twilight zone without a detailed step by step guide to assist you.
I would recommend:
http://www.geocities.com/jacksonmacd
download the first file – Security Paper by Jack Macdonald. It’s a little long but it tells you everything you need to know.
The 10 Commandments of Group Level Security
I - Don’t dismiss the Database Password.
Many databases only require a database password, not the Group or User Level Security function. This is especially true of stand-alone databases (only on one computer). Although the database password function may seem tame, many times it’s all that’s required.
While you may use both the Database Password function AND Group Level Security, you should really select one or the other. The two functions (Database Password and Group Level Security) are separate and distinct from one another. By using both, you are not adding to security, you are merely introducing a redundant step.
II - Security is always on.
You can’t turn it off. Even on an “unsecured” database, there is one default user in the security system. (see V)
III - Do not enter the deep waters of Access security without a guide.
Almost everything in Access can be learned by trial and error EXCEPT SECURITY.
Check the posts on security, then download the suggested articles and read them until you understand them. Pay special attention to Users, User Groups and Permissions; how to use them and how to create them. I still have to refer to my guide for assistance each time I use User or Group Level Security.
IV - Turn off your computer.
Now that you have made the decision to proceed with Group or User Level security, turn off your computer. Sit down with a paper and pencil. Figure out before you start what your User Groups are going to be and what permissions you need to assign to them. This is done by identifying the permissions people need to do their jobs.
Examples:
The clerks enter or update data and only require permissions to use the forms that let them do this. They don’t need permissions for anything else.
The president of the company on the other hand is given permissions to view or print all forms and reports.
You must then decide the permissions to be given to everyone in between. The company’s Accounting dept. needs permissions for receivable and payable forms and reports but they don’t require permissions for the clerk’s forms.
Create your User Groups first. Assign permissions to each User Group based on what the group does. You then create individual Users and assign them to the appropriate User Group. This will avoid having to assign individual permissions to every user.
Some User Groups may have a dozen Users, others my have only one.
V - The User “Admin” is no one and everyone.
The person who decided to use that name should be severely chastised. When you start security you will notice that the user Admin is already there. First time around, most people think they’re OK because they assign themselves as Admin based on the false impression that a user named Admin must have special rights and permissions. Admin is only the “Default User”.
By the way, don’t confuse the user Admin with the User Group Admins (notice the “s” in the User Group).
VI - Make a copy of your database and use that.
You will probably mess up the security at least once. While you can undo or make corrections to your secured database, if its’ totally fouled up (which it will be the first few times you try Security), it’s easier to just delete the database and start again on another copy. Don’t forget to delete your .mdw file as well (see IX).
VII - The Owner
When you create a database, Access assigns an owner. The owner is the person who was logged on at the time the database was created. As “Admin” is the default User (See V) “Admin” is normally the database owner.
Regardless of what restrictions you may subsequently place on “Admin”, “Admin” can’t be restricted – after all, he owns the database.
The Final Step in Group Level Security is to make your User name the owner and to delete “Admin”.
VIII – The Administrator
How many people spend their entire career with the same company anymore? When you leave the company you are currently with, it’s likely they will still be using your database. Create an Administrator who has permissions for everything. Place the User Name and Password in a sealed envelope and leave it with your supervisor. If the supervisor leaves the company, change the User Name and Password of your “back door” and give the new information to the new supervisor. This will avoid one of the most common Access Security questions which begins “I inherited this secured database but I can’t make any changes”.
IX - Create a new .mdw file.
Security settings are saved in a Workgroup file which has the extension .mdw The default is System.mdw which is usually located in C:Windows/Settings of your computer.
Each secured database should have its own .mdw file which should be saved in the same folder as the database. When creating it, give it a name other than System.mdw as this may cause problems down the line.
You must include this new .mdw file in the path on the shortcut to the secured database. Anyone trying to open the database will receive a password challenge.
X - Never make changes to the original database.
One of the reasons we used a copy of the database to install security was to leave an intact original or master copy which is kept in a secure area. When changes are required, make them to a copy of your master. When everything in the copy is working properly, make the copy your new master. This way you’ll always have somewhere to go if a disaster occurs.
When backing up, you only need to copy the data that’s in the tables. All the forms, queries, reports, macros etc. are already backed up in your master.
