Password Protect a form in MS Access 2003

Question

I am trying to create a login for my database and I don't want to use the Microsoft way of doing so. I want to have the users login with a username and password then have that information verified in the "tblUsers" table.

UserID   LoginID   Level    LevelID
jpurk    jack23     admin    3
krimes   kitty      editor   2
lwalms   low34      reader   1

I got as far as verifying the "UserID" and "LoginID" using dlookup

Nz(DLookup("[LoginID]", "tblUsers", "[UserID] = '" & Me.txtUserID & "'"), "")
 =Me.txtPassword

The problem I have now is that I want certain items on the menu unavailable to users without the proper Level; If they are only an "editor" or a "reader", then I don't want them to have access to the "administrative" button where I have placed all admin forms.

After I use dLookup to verify the username and password, how do I now find out their "Level" and assign rights to different menu items? Thank you.

You realise this is worthless as any kind of meaningful security measure. In my view it's very wrong to deceive your users in this way - giving naive users the illusion that a system is "secure" when it isn't. — nvogel

HansUp HansUp · Accepted Answer · 2011-04-26T19:09:51

Assuming your DLookup has found an existing LoginID value, you can use another to retrieve that user's LevelID. Then enable/disable the administrative command button based on their LevelID.

I'll suggest something like this in Form Load:

Dim lngLevelID As Long
lngLevelID = DLookup("[LevelID]", "tblUsers", "[LoginID] = " & Me.txtLoginID)
Me.cmdAdmin.Enabled = (lngLevelID = 3)

Notes: That assumes you've previously loaded the user's LoginID number into a text box named txtLoginID. txtLoginID could be hidden if you don't want the users to see it. Or you can grab the LoginID value by some other method.

If you have one-to-one matches between Level and LevelID, you shouldn't need to store both values in tblUsers. You can create a UserLevels lookup table to hold both, and store just the LevelID in tblUsers as a foreign key to the appropriate row in UserLevels.

Finally, the strategy you're using can work, but the security is shaky. As "guidance" to users willing to follow the rules, it's OK. But it can be easily circumvented by even unsophisticated users. Look for a different approach if your security needs are stringent.

Password Protect a form in MS Access 2003

1 Answers