Access Security with VB

[VBGuy74]

Hi...

I have been working on a project that uses MS Access as the backend for my database application. I need to have a function that will check whether the logon information (i.e. username, password) is correct. If false, then show logon form again and wait for the correct answer before opening db. I already set a password on the Access db.

Any feedback will be deeply appreciated.

Sincerely,

[JHausmann]

Do you need/want to use Access security (.mda/.mdw files) or are you planning on writing your own?

[JasonGS]

What ive done in the past is have the .MDB reside on a hidden server and share (\\server$\dbshare$\file.mdb) and in the client, a table with usernames and passwords. For a login check, id use something like in the code below...

Public Function LoginValid(Username As String, Password As String) As Integer
    
    ' Assume that adoConn is already opened to the DB file/server
    adoRset.Open "SELECT * FROM dbusers WHERE username = '" & Username & "'", adoConn, adOpenStatic + adOpenForwardOnly, adLockReadOnly
    
    If Not adoRset.EOF Then
        adoRset.MoveFirst
        If Password = adoRset("password") Then
            ' Valid username and password
            LoginValid = 100
        Else
            ' Valid username, password is invalid though
            LoginValid = 50
        End If
    Else
        ' Username is not valid
        LoginValid = 0
    End If
    
End Function

Private Sub Command1_Click()
    Select Case LoginValid(txtUsername.Text, txtPassword.Text)
        Case 0:    MsgBox "Invalid username you rat bastard!"
        Case 50:   MsgBox "Invalid password for the specified user!"
        Case 100:  MsgBox "You have been succesfully logged in!"
    End Select
End Sub