How to create a Multi User login with user levels (1 Viewer)

Brian62 · Sep 21, 2011

I need to create a multi-user with user levels such as admin, and read only access database I created. I curently made a login with a password to get in the database but I want to only allow certain people to just have read-only access. I may have to discard the login I created that I found on line to make this work. I have Access 2007 and I have the database split (FE and BE).

Is there a vbscript with directions that I can use to pull their network login as their username and the password I supply them which will give them the appropriate security level I set for them?

Thanks!

tonez90 · Sep 22, 2011

You could try setting up a login table with user level permissions (inlcuding logins and passwords). i.e. level 1=read only, 2=read/edit, 3=delete, 4=add etc. You coul dthen use a function to set the attributes of the form to allow edits, adds etc. If your on a network you may wish to use their network login and have alookuptable with their user name and user levels and simply do a comparison. I find method 1 better as it doesnt take as much maintanence and if you add new users it allows you control.

There are many examples of both methods (search "login") within this forum. If I get a chance I will strip down one of mine and post it for you.

mdlueck · Sep 22, 2011

tonez90 said:
You could try setting up a login table with user level permissions

I did basically that for the database with SQL Server back end that I am developing for a corporate client.

Everyone rides on the one shared SQL Server ID. Perms are all at the FE DB level.

Access grabs the Windows ID and looks that up in an application perms table. "No match, no access / dialog stating "go see an app admin to get your ID allowed to access the app".

If a match is found, then FE perms granted based on what perms that ID had granted to it.

Thus totally avoided "one more password to keep track of"...

CBrighton · Sep 22, 2011

No need to pull network password as they won't be able to log on, let alone open a database without supplying Windows with the correct password.

To pull the Windows logon name put this function in a module:

Code:

Option Compare Database
Option Explicit
 
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

Brian62 · Sep 22, 2011

Thanks everyone but I created a login by using this website:
http://www.about-access-databases.com/dialog-box-form.html

What I need to do is if I can is use this login (if not I'll create another one) and with the windows login create the read only, edit, admin (full access) so I can control who I want to do what on the database. Only two people will have admin access while the others will have read only. And if I can have certain people have access to certain forms to input data. Thx!

mdlueck · Sep 22, 2011

The downside to the Rx you used is it is yet another password for people to keep track of.

My solution was to trust what ever ID happened to be logged into Windows and assume that is who is using the Access DB. Thus I opted in the direction of Single-Sign-On.

Brian62 · Sep 22, 2011

Tonez90, I agree but I need to be able to build the vba coding, form and table to give the user the ability to login set by permissions granted.

CBrighton · Sep 23, 2011

It's a simple matter of altering the code behind the ok button.

The code on the linked website is:

Code:

[COLOR=#00e0][COLOR=#00e0]Private Sub cmd_OK_Click() [/COLOR][/COLOR]
[COLOR=#00e0][B][COLOR=green]'test the stored password is = to the manually entered password[/COLOR]    [/B][/COLOR]
[COLOR=#00e0][B][COLOR=blue]If[/COLOR] Me.cbo_User.Column(2) = Me.txt_Password [COLOR=blue]Then[/COLOR]        [/B][/COLOR]
[COLOR=#00e0][B] DoCmd.OpenForm "frm_MenuSwitchboard", acNormal[/B][/COLOR]
[COLOR=#00e0][B] DoCmd.Close acForm, "frm_Login[/B][/COLOR]
[COLOR=#00e0][B][COLOR=blue]Else[/COLOR] [COLOR=green]'wrong match[/COLOR]        MsgBox "Wrong password entered." & _[/B]
[B]vbCrLf & "Please re-enter password.", _[/B]
[B]vbExclamation, "Invalid Password"[/B]
[B]Me.txt_Password.SetFocus [COLOR=green]'places the cursor in password control[/COLOR]   [/B]
[B][COLOR=blue]End If[/COLOR][/B]
[COLOR=#00e0]End Sub[/COLOR][/COLOR]

Instead of just opening the switchboard whenever the password is correct you could add a 3rd field to the username/password table which the combobox feeds from with the form name.

You can then use the following to open the form which is in the new field like this:

Code:

[B][COLOR=#00e000]DoCmd.OpenForm Me.cbo_User.Column(3), acNormal[/COLOR][/B]

Brian62 · Sep 23, 2011

I don't quite understand the last code to where I put it and what the third field is in the username/password table and what I put in that field.

CBrighton · Sep 26, 2011

The new field would be something like DefaultForm and would hold the name of the form which that person loads.

If the combobox is then changed from 2 column (with only the name column visible) to 3 column (with only the name visible), and refer to column 3 in your OpenForm command instead of specifying a form name like "frm_MenuSwitchboard".

Brian62 · Sep 26, 2011

I have a few questions. I can not get this working at all.

You wrote:
The new field would be something like DefaultForm and would hold the name of the form which that person loads.

Would this be a command button to my switchboard on the login form?

You wrote:
If the combobox is then changed from 2 column (with only the name column visible) to 3 column (with only the name visible), and refer to column 3 in your OpenForm command instead of specifying a form name like "frm_MenuSwitchboard".

Not sure what part of the form/boxes to change????

You wrote:
DoCmd.OpenForm Me.cbo_User.Column(3), acNormal
Not exactly sure where to put this code. Is it in my login form?

I don't see how the windows login will work with the changes.. I'm sure I am missing something.

CBrighton · Sep 27, 2011

Lets clear up whether it's a viable idea first.

I'm assuming your PC / network has some security and therefore everyone has a unique windows / network login? If so there's no need to ask for another usename and password if you can check who they have logged in as.

If not then you can ignore my posts.

mdlueck · Sep 27, 2011

CBrighton said:
I'm assuming your PC / network has some security and therefore everyone has a unique windows / network login? If so there's no need to ask for another usename and password if you can check who they have logged in as.

Bing bing bing bing!!! Exactly what I suggested above.

Just grab the Windows userid and use that unique piece of information to check application permissions in a table defining the perms. I happened to use Binary OR logic to encode a perm mask into a single LONG number. The system finds a matching userid, then sees what perms the user has for the system and grants access based on those perms. I keep the app credentials floating around in a application global object so that all parts of the application may check permissions.

Brian62 · Sep 27, 2011

I like the idea of using the windows login and use a table to give the type of permissions each user would have. My question is, is there a sample databse or a script that also describes how to set it up. All great ideas but this is what I would like to do. Thanks!!!

P.S. Is there a script that will hide "All Access Objects" that will only be accessable with a password to the administrator?

CBrighton · Sep 28, 2011

mdlueck said:
Bing bing bing bing!!! Exactly what I suggested above.

Also exactly what the function I posted does (pull the windows login name).

However the OP sounded a little confused, so further discussion was required.

How to create a Multi User login with user levels (1 Viewer)

Brian62

Registered User.

tonez90

Registered User.

mdlueck

Sr. Application Developer

CBrighton

Surfing while working...

Brian62

Registered User.

mdlueck

Sr. Application Developer

Brian62

Registered User.

CBrighton

Surfing while working...

Brian62

Registered User.

CBrighton

Surfing while working...

Brian62

Registered User.

CBrighton

Surfing while working...

mdlueck

Sr. Application Developer

Brian62

Registered User.

CBrighton

Surfing while working...

Similar threads

Users who are viewing this thread