Setting up Access Security for a database on a network 1

[shauntck]

Hey guys, i have an access database on a network and need to set up restrictions. I have done this and on my PC everything works fine however, when I go to another computer and attempt it complains the user doesn't have access.

Any ideas how I can solve this?
Thanks for your help!

-Shaunt

 

[csimms]

Not enough information for me to troubleshoot, but here is a link that tells you everything you should know about Access security implementation:

http://www.geocities.com/jacksonmacd/AJMAccessSecurity.pdf

I found this a lot more detailed than what I was able to find at the Microsoft knowledgebase.
Carla

 

[ptpaton]

Shaunt,

I ran into a similar problem because I was not forcing my users into a secure workgroup. I solved this problem by creating a shortcut, hiding the actual database, and requiring users to log in using the shortcut. Here it is - replace the paths with your network paths:

"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" "S:\Your Path\...\YourDB.mdb" /WRKGRP "S:\YourPath\...\Secured.mdw"

The C: statement opens Access. The S:\YourDB.mdb opens the actual database from the server, and the /WRKGRP S:\Secured.mdw forces the user to log into the secure workgroup. Don't forget the quotes around the paths!

If you need help with Workgroups, post again!

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[shauntck]

Patrick, I think that will work for creating a shortcut to the database however, i need to have shortcuts to the specific forms in the database. Is there a way to create the shortcut to the form itself and still give it the security argument? I also need it to load the form and if possible leave the database window/additional forms etc hidden.

Thanks for your help so far, i much appreciate it!

-Shaunt

 

[ptpaton]

Hi Shaunt,

If I'm following you, what may work for you is to have a main menu or switchboard form with command buttons that open your other forms.

All of the options you're looking for can be found under tools->startup. From there, you can disable hot keys (Like F-11), hide the database window, disable and customize menus... You can also set a form to automatically load on open of the database.

What I would do is build an unbound form called frmMenuMain with command buttons for each form you want to open. Use DoCmd.OpenForm "frmName" to open each form you're interested in, keeping frmMenuMain open in the background. That way, when a user closes the form they're using, they are returned to the main menu.

To open on startup, select the form under the "Display Form/Page" combo box.

To hide the DB window, uncheck "Display Database Window"

To disable menus, uncheck "Allow Full Menus"

To disable special keys, uncheck "Use access special keys"

For the average user, this will lock the database down to only those forms you allow them access to.

IMPORTANT: If you disable the shortcut menu, you'll have trouble getting into design mode of the form. To get around this, hold <Shift> when you open the database and the startup options will be bypassed.

HTH! Lemme know...

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[shauntck]

Patrick, quick question... i'm trying to get the username of the person logged in to the access database in a form.

This statement should get me the username i thought however, it is not working correctly. Do you know of another way to do this?

sUser = VBA.Environ("USERNAME")

Thanks,
-Shaunt

 

[shauntck]

scratch that last question, it does work. The problem is i don't need the windows username necessarily. I need the name that I logged into Access using (which is hard coded in the shortcut to the database)

Thanks for all your help so far. It is much appreciated!
-SHaunt

 

[ptpaton]

Shaunt,

CurrentUser() will also tell you who is logged into the workgroup from a given computer.

It's kinda cool because as you get into it, you can user CurrentUser() to lock down recordsets so that only the user who created the data can modify a given recordset. Very useful if you're worried about one user overwriting another's data.

Let me know if you want to take it that far.


-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[shauntck]

yes, that is something i had considered. I didn't realize that there was a solution like that. So what, you add a record to the database for the username and then compare username upon change or something like that? or is there a standard way that takes care of it easier?

-Shaunt

 

[ptpaton]

Hey,

No, there's no standard way. I generally use two forms: a form to edit new data and a second form to look up data through a combo box. The lookupu form has an identical query to the new entry form, except that it references the combo box on a lookup menu to filter only the record that the user wants to edit. Once you have the lookup form, here are the steps I take to make it work:
On both forms:
1. Create a text field in your table called [User]
2. Create a text or combo box on your form bound [User] called txtUser
3. Have the user select or enter their name in txtUser
4. Continue data entry as usual

Now, on the lookup form:
1. On Open, use code similar to the following to open the form as read only. I don't have the exact code in front of me, but it's something like:
If Not Me.User=CurrentUser() Then
DoCmd.OpenForm ,,,acReadOnly

This way, a user essentially "marks" the record as their own, and if anyone else tries to access it, the dataset is opened as read only.

I can give you better instuction when I'm in my office tommorrow, but that's a start!

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[willir]

A lot of stuff here, and some of it is going in different directions...

Here is a FAQ by MS on security...
ACC: Microsoft Access Security FAQ Available in Download Center

TekTips also has some pretty good FAQs on security..
Gauging your security needs; alternatives to Access/JET security

Who's in the database? User List and Passive Shutdown

How do I get and use the Windows Login ID?

I am not sure why your...
sUser = VBA.Environ("USERNAME")
...does not work. I have used in Novell, NT, Win2K, XP, ActiveDirectory networks without problems. This provides the network login name.

CurrentUser() returns the database login name. If you do not have security in place, this will default to "Admin". You have to make sure that user are using logging into the Access database, and using their correct security and security group.

I feel using network security is a pretty good way of setting up Access security. The user logs on to the network, and is automatically given their rights within the Access database per their network login. Then, for admin access, a special form is used to change the Access login.

Now controlling what forms to use per their security settings... I see two ways: a) Use different front ends; b) The openning form checks the user's login name and then hides the first form, and controls the second form to be opened per the user's requirements.