Solved: Create a simple login screen for Access

[ladysnake38]

Hi:

I need to create a very simple login screen for the program I'm working on. The users table has only 3 fields, user_id, name and password. Nothing futher is needed.

The login screen has two unbound boxes Name and Password. If the name (which is in the format of joesmith) and the password (limit 12 characters) are in the table, the user pushes the continue button and the main form will open. If not, an error message "Invalid name or password, please try again" will appear. It would be nice if after 3 attempts, it would kick you out, but its not necessary.

I have read in previous posts about using DLookup, but I'm very new to Access at this level and never have used VB before.

Can anyone show me an example of the code?

Cheryl

[nczimm]

Here is some code I use. The call to faillogin is a function that opens a form with a failure message. There is a lot of code between what I have provided and the faillogin function that will probably be very confusing. If you like I can send it. I also have cut out a lot. If something seems to be missing I probably deleted it in error. I' be happy to help further.

Dim dbconn As ADODB.Connection
Dim rst As Recordset
Dim clogin As String
If Me.login <> "" Then
If Me.password <> "" Then
clogin = Me.login
Set dbconn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "select * from users where login=" & "'" & clogin & "'", dbconn, adOpenKeyset, adLockOptimistic
If Me.password = rst!password Then
action = "OK"
DoCmd.Close
Else:
Call faillogin
End If
End If
End If

[nczimm]

Here is some code I use. The call to faillogin is a function that opens a form with a failure message. There is a lot of code between what I have provided and the faillogin function that will probably be very confusing. If you like I can send it. I also have cut out a lot. If something seems to be missing I probably deleted it in error. I' be happy to help further.

Dim dbconn As ADODB.Connection
Dim rst As Recordset
Dim clogin As String
If Me.login <> "" Then
If Me.password <> "" Then
clogin = Me.login
Set dbconn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "select * from users where login=" & "'" & clogin & "'", dbconn, adOpenKeyset, adLockOptimistic
If Me.password = rst!password Then
action = "OK"
DoCmd.Close
Else:
Call faillogin
End If
End If
End If

[ladysnake38]

Forgive me for looking like a complete idiot...and believe me I feel like it right now.

How does it tie into the unbound fields? The login screen appears when they first open the database. I have sample users and passwords in a table (tblusers).

I guess what I'm asking is how does it work from the login screen where I entered the name joesmith and the password newkid.

I was thinking IF joesmith is in table tblusers.username THEN
If newkid is in table tblusers.password THEN
DoCmd OpenForm "mainform"
Else
msgbox "Invalid Password"
End IF
Else
msgbox "Invalid Name"

End If.

I say an example where they used Unbound Control:users but since I have two didn't know how it would distinguish between the two unbound control boxes.

I admit, I'm really confused at this point.

I appreciate your help greatly.

Cheryl

[ladysnake38]

So far this is the code I have and it does not work. Where does it verify the password validity??



Private Sub VerifyandOpen_Click()
On Error GoTo Err_VerifyandOpen_Click
Dim dbconn As ADODB.Connection
Dim rst As Recordset
Dim clogin As String
Dim stDocName As String
Dim stLinkCriteria As String

If Me.login <> "" Then
If Me.password <> "" Then
clogin = Me.login
Set dbconn = tblusers.username
Set rst = New ADODB.Recordset
rst.Open "select * from tblusers.username where login=" & "'" & clogin & "'", dbconn, adOpenKeyset, adLockOptimistic
If Me.password = rst!password Then
Action = "OK"
stDocName = "frmntwnar"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else:
MsgBox "Invalid Name or Password)"
End If
End If
End If

End Sub

[DarkSprout]

Here is a stripped down database with just the Login screen, you should find it rather secure - once you login with Standard User permission [type1] the database won't open unlocked when holding down shift.
only an Admin can do this [type2] for this to unlock, an admin has to Login, then exit, and then open the database using the shift bypass.

Password = Password1 <-- change this...

Enjoy,

[X-BRichard-X]

<<< I need to create a very simple login screen for the program I'm working on. The users table has only 3 fields, user_id, name and password.>>>

Never EVER have passwords in an Access table - EVER! This is a significant security violation in MS Access development.

To increase the security of your database, hard code the passwords in VBA code, then password-protect the project file (VBA Editor) and then convert your MDB file to an MDE - MDE strips the code from your application and compiles it instead of interpreting it.

Now, even with these security measures, it may not be enough. Depending on how secure you need your database to be, the ultimate security would be to have all your passwords entered into an Access table BUT, and here's the exception to the rule I stated earlier, only have passwords maintained in an Access table that is ENCRYPTED.

I have the Rijndael encryption algorithm for VBA - and even if you use this high level of security, you will still need a VB.Net mini-application to independently encrypt-decrypt the keys used to encrypt-decrypt your MS Access tables.....did you get all that?

[ladysnake38]

Hi Richard. I do understand what you are saying but security wasn't the issue with this. They only want to track who is in the database. It was a minimum security issue, but I thank you for the explanation.

Thank you all for your assistance, I was able to create a very simple login screen as requested.