Results 1 to 12 of 12
  1. #1
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272

    Access login to be case sensitive

    i have a login form which logs in perfectly but i recently noticed that it still logs the user in without considering the case sensitivity of the password.
    Would be glad if someone could be of help.

    This is the code behind my login button on the form



    "Dim FIRST_NAME As Variant, access_level As Variant

    If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbExclamation, title:="SYSTEM"
    Me.txt_username.SetFocus
    Exit Sub
    End If

    If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbExclamation, title:="SYSTEM"
    Me.txt_password.SetFocus
    Exit Sub
    End If

    ' RETREIVE FROM SAVED QUERY
    ' ASSUMES EVERY USER GIVEN A NON-NULL ACCESS LEVEL
    FIRST_NAME = DLookup("FirstName", "tbl_login", "UserName = '" & Me.txt_username & "' and password = '" & Me.txt_password & "'")


    access_level = DLookup("access_level", "tbl_login", "UserName = '" & Me.txt_username & "' and password = '" & Me.txt_password & "'")



    If IsNull(FIRST_NAME) = True Then
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
    Me.txt_username.SetFocus
    Else
    MsgBox prompt:="Welcome, " & FIRST_NAME & ".", buttons:=vbOKOnly, title:="SYSTEM"

    'arnelgp
    'save the first_name and access_level to Tempvars
    TempVars("First_Name") = FIRST_NAME
    TempVars("Access_Level") = access_level
    TempVars("user") = Me.txt_username.Value


    ' CONDITIONALLY OPEN FORMS
    Select Case access_level
    Case "Administrator"
    DoCmd.OpenForm "ABC"


    Case "Accounts"
    DoCmd.OpenForm "XYZ"

    End Select
    End If

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,707
    Review strComp Binary comparison should achieve what you ask.

  3. #3
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by orange View Post
    Review strComp Binary comparison should achieve what you ask.
    So how do i apply it to my code?
    Would be glad if i could get a practical example using my code

    Am thinking of modification in the dlookup line in the code. But I don't know how to go about it

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,651
    You could compare as in If strComp(access_level,Me.txt_password,0) = 0 Then (good to go) but in such a way that you can know there is a value in Me.txt_password

    Code:
    If IsNull(FIRST_NAME) = True Then
       MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
       Me.txt_username.SetFocus
       Exit Sub
    End If
    
    If strComp(access_level,Me.txt_password,0) = 0 Then
       MsgBox prompt:="Welcome, " & FIRST_NAME & ".", buttons:=vbOKOnly, title:="SYSTEM"
    Else
       Msgbox "some other message"
       Exit Sub
    End If
    As others here probably know, I'm not a fan of all this password maintenance, resetting and stuff. Instead I'd just have user details in a table, including Windows user login name and pc name they happen to be on). When anyone opens the db if they're not found in the table they don't get in. In the past, I had people call me asking why they can't open the db. They had copied it from the network figuring they could just open it and poke around. If they had got in, it would have closed on them anyway because their copy location would not match the location of the authorized version.

    EDIT - the pc name wasn't in the user table. It was stored in the login table record.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by Micron View Post
    You could compare as in If strComp(access_level,Me.txt_password,0) = 0 Then (good to go) but in such a way that you can know there is a value in Me.txt_password

    Code:
    If IsNull(FIRST_NAME) = True Then
       MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
       Me.txt_username.SetFocus
       Exit Sub
    End If
    
    If strComp(access_level,Me.txt_password,0) = 0 Then
       MsgBox prompt:="Welcome, " & FIRST_NAME & ".", buttons:=vbOKOnly, title:="SYSTEM"
    Else
       Msgbox "some other message"
       Exit Sub
    End If
    As others here probably know, I'm not a fan of all this password maintenance, resetting and stuff. Instead I'd just have user details in a table, including Windows user login name and pc name they happen to be on). When anyone opens the db if they're not found in the table they don't get in. In the past, I had people call me asking why they can't open the db. They had copied it from the network figuring they could just open it and poke around. If they had got in, it would have closed on them anyway because their copy location would not match the location of the authorized version.

    EDIT - the pc name wasn't in the user table. It was stored in the login table record.
    Thanks for your feedback.
    This method will have to require me changing the whole code since the check on user details is on the dlookup line of the code.

    I strongly believe that modifying that line with the StrComp might fix the issue.

    I tried your code but user details wont login

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,651
    This method will have to require me changing the whole code since the check on user details is on the dlookup line of the code.
    Not sure what that means, or if you realize that suggestion would be integrated into what you have, not replace what you have.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by Micron View Post
    Not sure what that means, or if you realize that suggestion would be integrated into what you have, not replace what you have.
    I modified the line of the code at the section you sent me but the login details didnt login

  8. #8
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by Micron View Post
    Not sure what that means, or if you realize that suggestion would be integrated into what you have, not replace what you have.
    So do you suggest integrating to what I have?
    If thats the case, which line of the code should I place it?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,651
    Look at post 4. I showed you how to modify the prior If block and where to put the strComp test.
    Best if you don't simply say things like 'I used your code but it didn't work' but instead, show us - and please use code tags (# button on posting toolbar) to maintain indentation and readability as I did.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,707
    Here's a mock up to simulate testing for a case sensitive password match

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: testStrcomp
    ' Purpose: Routine to demo the use of strComp with binary compare.
    ' in this sample
    '   the username is not required to be case sensitive
    '   the password is case sensitive.
    
    
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Author: Jack
    ' Date: 04-May-23
    ' ----------------------------------------------------------------
    Sub testStrcomp()
              Dim accesslevel As String
    10        Dim EnteredUsername As String: EnteredUsername = "Joe User"  'different capotalization since NOT
    20        Dim storedUserName As String: storedUserName = "Joe USER"    'case sensitive
    
    30        Dim storedPassword As String: storedPassword = "$MyPasSwOrd"  'the valid stored password
    
              Dim enteredPassword(2) As String  ' 3 attempts to enter a password
              
              Dim i As Integer
    
              'represents 3 entered passwords
    40        enteredPassword(0) = "smypassWord"  'invalid no $ improper capitalization
    50        enteredPassword(1) = "$MyPasSwOrd"  'Valid
    60        enteredPassword(2) = "$MyPasswOrd"  'one s is not capitalized
    
              'access_level = DLookup("access_level", "tbl_login", "UserName = '" & Me.txt_username & "' and password = '" & Me.txt_password & "'")
    
              'simulate the test
              'password entered must match storedPassword CASE SENSITIVE
    
    70        For i = LBound(enteredPassword) To UBound(enteredPassword)
    80            If (EnteredUsername = storedUserName And StrComp(storedPassword, enteredPassword(i), vbBinaryCompare) = 0) Then
    90                Debug.Print i & "  Valid login for " & EnteredUsername & " and  Password "
    100           Else
    110               Debug.Print i & "  !!! INValid login for " & EnteredUsername & " mismatched  Password ---validPwd " _
                          & storedPassword & " vs " & enteredPassword(i)
    120           End If
    130       Next i
    End Sub
    'RESULT
    0 !!! INValid login for Joe User mismatched Password ---validPwd $MyPasSwOrd vs smypassWord
    1 Valid login for Joe User and Password
    2 !!! INValid login for Joe User mismatched Password ---validPwd $MyPasSwOrd vs $MyPasswOrd


    NOTE: Username is not case sensitive so Joe User matches Joe USER

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,925
    I was approached by the OP via a PM at Access World Forums where he goes by the name of @Tiprof.
    I asked him to post as a forum thread so others could participate/benefit & he then referred me to this post

    My response below was written before seeing this thread and much of it covers the same ground as previous answers...

    --------------------------------------------------------------------------------------------------

    By default, Access is case insensitive.

    It is certainly possible to do a case sensitive check, by using StrComp(EnteredPasswordString, StoredPasswordString, vbBinaryCompare). The output will be -1, 0 , 1 or Null depending on the two string values. Output = 0 means both are IDENTICAL including the case of each character and passes the check. Any other output indicates differences so the check should fail

    For more details, see the MS Help article on the StrComp function

    However, there is a better approach with an important additional step that will also make your passwords more secure
    Passwords should NEVER be stored unencrypted in a database. If anyone gains access to user passwords, you will have major data protection issues. Don't risk it!

    Ideally, check the passwords against the active directory which is designed to be secure. If so, no login form is normally needed.

    However, if you do want to utilise a login system, encrypt the passwords with a suitable encryption method like RC4.
    See my PasswordLogin example database

    To do this, each password is stored encrypted using the function RC4(PasswordString, RC4_Key) where RC4_Key is the encryption string. This has the benefit of giving different results depending on the case used. For example:

    ?RC4("opensesame", "isladogs")
    ~—Kkٱ

    ?RC4("OpenSesamE", "isladogs")
    ~Kk‘

    ?RC4("OPENSESAME", "isladogs")
    0^kK.‘

    ?RC4("oPeNsEsAmE", "isladogs")
    0^—kk‘

    If you look carefully, all of these are subtly different
    Then your password check process should do a binary compare of the encrypted version of the entered password with the stored password.

    EDIT:
    I have just used the above approach to update my example app and make the password validation checks case sensitive
    Last edited by isladogs; 05-06-2023 at 07:32 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    I have been able to fine a fix with this code:

    FIRST_NAME = DLookup("FirstName", "tbl_login", "StrComp(username, '" & Me.txt_username.Value & "', " & vbBinaryCompare & ") = 0")
    access_level = DLookup("access_level", "tbl_login", "StrComp(password, '" & Me.txt_password.Value & "', " & vbBinaryCompare & ") = 0")

    If IsNull(FIRST_NAME) = True Then
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
    Me.txt_username.SetFocus
    Exit Sub
    End If

    If access_level = DLookup("access_level", "tbl_login", "StrComp(password, '" & Me.txt_password.Value & "', " & vbBinaryCompare & ") = 0") Then

    MsgBox prompt:="Welcome, " & FIRST_NAME & ".", buttons:=vbOKOnly, title:="SYSTEM"

    Else
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
    Exit Sub

    End If
    Thanks to you all for your suggestions

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Case Sensitive Data
    By VLPOTTER in forum Access
    Replies: 1
    Last Post: 04-28-2021, 09:31 AM
  2. Case sensitive login
    By CHEECO in forum Access
    Replies: 7
    Last Post: 02-09-2018, 11:43 AM
  3. Case sensitive query
    By Panzerattack in forum Queries
    Replies: 3
    Last Post: 02-11-2017, 12:15 PM
  4. Case Sensitive Inner Join
    By Rriemer in forum Queries
    Replies: 3
    Last Post: 10-13-2015, 01:24 PM
  5. is access case-sensitive?
    By pen in forum Programming
    Replies: 1
    Last Post: 04-07-2009, 05:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums