Looking for help on how to add Multi User Passwords

[JustDonny]

Access 2010


First post and great resource BTW! I'm currently working with a template. The template is called "Issues Database" its found under New>Issues Web Database. I have it already setup for what I need. However I'm looking for help on how to add passwords to the users. Something simple and basic. There will only be a few Users.

[JeroenMioch]

Access has a build in password feature, you can find it on the "file" tab when opening the database in design mode.

[JustDonny]

Access has a build in password feature, you can find it on the "file" tab when opening the database in design mode.

I may be wrong but I think you're talking about putting a password on the entire database. I'm looking for User passwords. When the data base is loaded up you are prompted to sign in. I'm looking to add multiple users and passwords that go along with the users


.

[JeroenMioch]

Im pretty sure its a build in feature. Maybe not at the direction i gave you maybe.

If your looking for a custom one i can provide it for you, along with failed attempts and everything that goes along with it. Admin can give out passwords and users can change theirs. You even can make a password valid untill a certain date. Furthermore the db is protected against attempts to go in editting mode.

Let me know if your interested

[JustDonny]

I'm looking to add passwords for the users. In the screen shot above it has their office email and then their name. When they select the line it will highlight it and they will be able to log into the database. What I'm trying to create is a form... or anything really.. to pop up after they select login prompting them for their password with an "Ok" button. If the password is accepted it will bring them to the main database. There will be 12 users and 12 different passwords.

[Micron]

Have you considered just allowing access based on a table of users (i.e. if they're not in the table, they don't get in)? You just find their login id during startup. The advantage is that you can create user levels and control access to certain forms, reports or functionality based on their profile. You may not be considering that now, but later on you might wish you had. Regardless of which path you take, your ability to control access is limited to how much protection you wrap the database in. This also avoids the common problem of managing passwords - y'know, they are forgotten frequently.

[JeroenMioch]

What your trying to accomplish is fairly simple. Later you can crank it up a notch like we suggested.

Make a table calle tblPasswords for example.
In this table you can have two fields called UserName and PassWord.

Offcourse you put the login credentials in this table, like in your example Test@Test.com in your the field UserName and in the Password field the word Test.

Lets assume your Login form is called frmLogin and the two controls on this form ctrlUsername and ctrlPassword.

code is as follows :

'Check if the password field is not empty
If Len(Nz(Me!ctrlPassword, "")) = 0 Then
   MsgBox "Password is required", _
      vbInformation, "Missing password"
         Me.ctrlPassword.SetFocus
       Exit Sub
    End If

'the same code can be used to check if the username is empty.

if DLookup("Password", "tblPasswords", "[Username] = '" & Me[.ctrlUserName].Value & "'") = me.ctrlPassWord.value then
DoCmd.OpenForm "frmYourForm"
else
   MsgBox "Wrong Password", _
      vbInformation, "Password notification"
         Me.ctrlPassword.SetFocus
       Exit Sub
    End If

If your users all use their own computer, a more userfriendly way is to use the network or computername.
You can fetch this information failry simple using =FOSusername()

[ItsMe]

I find it easiest to use the Windows User profile. If a user signs into windows, they are likely to have access to the backend file anyway. So, instead of storing and managing passwords in Access, manage User names in an Access tables and compare the Windows User against the names in the Access table.

One way to determine the current Windows User is the Environ() function. Another approach is to interrogate Active Directory. If you use the Environ function, it can be difficult sometimes to determine the directory path of the User Profile when multiple networks are used with the same login name or the same login name has a profile on the local computer as well as a profile in AD (over the network).

[Micron]

FOSusername() is a call to a module level function which you didn't supply or provide a link to. It can be found here:
http://access.mvps.org/access/api/api0008.htm
It's the same way I get the user name as I mentioned, without using any login form or procedure. Seems like too much bother for the user and developer AFAIC.
I assign the function value to a variable and create a custom user object with about 6 properties, but to keep it simpler here...

strUserName = fosUserName
If Not IsNull(strUserName) then 
  strStatus = DLookup("Status","tblUser","User='" & strUserName & "'")
  If IsNull(strStatus) then 
     strMsg = "You are not a registered user or your account is inactive. Please call a database administrator to obtain program access." & vbCrLf
     strMsg = strMsg & "Program will close now."
     msgbox strMsg
  End If
End If

You could repeat the lookups for any number of user associated values; perhaps even assign them to a global variable. You could also open a form with a timer instead of using a message box, present any message, and close the form using the timer. The close event of the form can include Application.Quit to close the database. I realize this is not a whole lot less code, but it does provide a basis for dealing with user access and permissions for forms, reports or whatever.

P.S. re the Environ function - I've seen cases where it's a bit wonky, but can't recall why. I know people who have encounterd issues with it (due to some sort of network modification, I think) and gave it up for the above named function.

[JustDonny]

What your trying to accomplish is fairly simple. Later you can crank it up a notch like we suggested.

Make a table calle tblPasswords for example.
In this table you can have two fields called UserName and PassWord.

Offcourse you put the login credentials in this table, like in your example Test@Test.com in your the field UserName and in the Password field the word Test.

Lets assume your Login form is called frmLogin and the two controls on this form ctrlUsername and ctrlPassword.

code is as follows :

'Check if the password field is not empty
If Len(Nz(Me!ctrlPassword, "")) = 0 Then
   MsgBox "Password is required", _
      vbInformation, "Missing password"
         Me.ctrlPassword.SetFocus
       Exit Sub
    End If

'the same code can be used to check if the username is empty.

if DLookup("Password", "tblPasswords", "[Username] = '" & Me[.ctrlUserName].Value & "'") = me.ctrlPassWord.value then
DoCmd.OpenForm "frmYourForm"
else
   MsgBox "Wrong Password", _
      vbInformation, "Password notification"
         Me.ctrlPassword.SetFocus
       Exit Sub
    End If

If your users all use their own computer, a more userfriendly way is to use the network or computername.
You can fetch this information failry simple using =FOSusername()

PM Sent....