create case sensitive login in access 2007 and VB6

Question

I have saved a record in Access 2007 table under the fields username and password. Now i want to check whether proper case has been entered in the VB6 text boxes to validate the username and password from access table. Please help me in this regard. Thank you

Sarfaraz

possible duplicate of [How to make SQL case sensitive](http://stackoverflow.com/questions/5747698/how-to-make-sql-case-sensitive) — Marek Grzenkowicz, Dec 31 '12 at 07:26

Fionnuala · Answer 1 · 2012-12-31T12:04:33.583

StrComp may suit:

Sub TestMatch()
    'string1 is less than string2    -1
    'string1 is equal to string2      0
    'string1 is greater than string2  1
    'string1 or string2 is Null      Null

    Debug.Print StrComp("ABC", "AB", vbBinaryCompare)  ''=  1
    Debug.Print StrComp("ABC", "abc", vbBinaryCompare) ''= -1
    Debug.Print StrComp("ABC", "ABC", vbBinaryCompare) ''=  0
    Debug.Print StrComp(Null, "ABC", vbBinaryCompare)  ''= Null

End Sub

See also : http://support.microsoft.com/kb/209674

score 1 · Answer 2 · answered Dec 31 '12 at 09:42

You may get some posts which make use of RegEx, not something I really use so this function can help you determine whether two strings match and is case sensitive.

Public Function ExactMatch(varFirst As Variant, varSecond As Variant) As Boolean

Dim inti As Integer

    'Initialise to False and amend to True if function passes
    ExactMatch = False

    'Initial checks before proceeding (Null?, Length mismatch?)
    If IsNull(varFirst) And IsNull(varSecond) Then
        ExactMatch = True
        Exit Function
    ElseIf IsNull(varFirst) And Not IsNull(varSecond) Then
        Exit Function
    ElseIf Not IsNull(varFirst) And IsNull(varSecond) Then
        Exit Function
    End If

    If Len(CStr(varFirst)) <> Len(CStr(varSecond)) Then Exit Function

    'Begin
    For inti = 1 To Len(CStr(varFirst))
        If Asc(Mid(varFirst, inti, 1)) <> Asc(Mid(varSecond, inti, 1)) Then Exit Function
    Next

    ExactMatch = True

End Function

score 0 · Answer 3 · answered Dec 31 '12 at 17:20

I see people throwing in all kinds of fancy string comparing code and functions... I am puzzled, why not just compare 2 variables?

Variables strUserName_Entered and strPassword_Entered are entered by user and variables strUserName_DB and strPassword_DB are loaded from DB.

If strUserName_Entered = strUserName_DB AND strPassword_Entered = strPassword_DB Then
    ' Everything is OK, entered username/password combination is exactly the same as the one from DB
Else
    ' Username/password combination do not match

End If

If you specifically want to differentiate when user enters the right username/password but in wrong case then you can use this

If strUserName_Entered = strUserName_DB AND strPassword_Entered = strPassword_DB Then
    ' Everything is OK, entered username/password combination is exactly the same as the one from DB
Else If UCase(strUserName_Entered) = UCase(strUserName_DB) AND UCase(strPassword_Entered) = UCase(strPassword_DB) Then
    ' Username/password combination match, but at least one or more characters is/are in a wrong case

Else
    ' Username/password combination do not match (not the case error)

End If

Because of the way Access is usually set up, see http://stackoverflow.com/questions/13996156/string-string-comparison-style-theory/13996199#13996199 — Fionnuala, Jan 01 '13 at 20:36
@Remou Thanks. I've never used that option, nor have I seen in it in my 10 years of programming. — George, Jan 02 '13 at 14:36

score 0 · Answer 4 · answered Jan 01 '13 at 04:12

0

I have the answer finally:

Connection

Set rs = New ADODB.Recordset
Qry = "SELECT Password FROM Login WHERE UserName = '" & Text1.Text & "'"
rs.Open Qry, Con, adOpenDynamic, adLockOptimistic
If Not rs.EOF Then
    If rs(0) = Text2.Text Then     
       msgbox"OK"
       exit sub      
    Else
       MsgBox "Invalid Username or Password", vbInformation, "Login..."

    End If
Else
    MsgBox "Invalid Username or Password", vbInformation, "Login..." 
End If

Enjoy coding

answered Jan 01 '13 at 04:12

user1939189

9
1
5

You need to send only username to database and get password from database and finally compare with if statements. – user1939189 Jan 01 '13 at 04:13
2

How is that case sensitive? If you're using `Option Compare Binary` in your code module's Declarations, you should make that point clear. – HansUp Jan 01 '13 at 15:49
I agree with HansUp, the OP is a bit confusing, since proper case was mentioned. OA, can you please clarify? On a side not, to optimize the code, you have 2 error message boxes - 1 is unnecessary. Erase 1 and move the other to after the second `End If` (right at the end of your code) – George Jan 02 '13 at 14:40

create case sensitive login in access 2007 and VB6

4 Answers4