Access Database Users And Permissions Example

This post is a compilation of two of my previous posts:

http://vbahowto.com/ms-access-object-security/

http://vbahowto.com/ms-access-login-form-revised/

This elaborates on the two posts a bit:

First, here are all of the database objects:

There are 2 tables here:

tblLogin and tblAdmin

Here are the main forms:

frmLogin – this is the form that opens when the database opens

Here is the code that gets executed when the “Login” button is clicked:

Private Sub btnLogin_Click()
    Dim rst As Recordset
    Dim strSQL As String
    
    strSQL = "SELECT UserName, UserPWD FROM tblLogin WHERE UserName = '" & Me.txtName & "' AND UserPWD = '" & Me.txtPWD & "'"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    If Not rst.EOF Then
        'open
        m_LoggedInUser = Me.txtName
        DoCmd.Close acForm, Me.Name
        
        MsgBox "Welcome: " & m_LoggedInUser
        
        DoCmd.OpenForm "frmMain"
    Else
        MsgBox "Your password is wrong"
        
    End If
    
    
End Sub

Private Sub btnLogin_Click()

Dim rst As Recordset

Dim strSQL As String

strSQL = "SELECT UserName, UserPWD FROM tblLogin WHERE UserName = '" & Me.txtName & "' AND UserPWD = '" & Me.txtPWD & "'"

Set rst = CurrentDb.OpenRecordset(strSQL)

If Not rst.EOF Then

'open

m_LoggedInUser = Me.txtName

DoCmd.Close acForm, Me.Name

MsgBox "Welcome: " & m_LoggedInUser

DoCmd.OpenForm "frmMain"

Else

MsgBox "Your password is wrong"

End If

End Sub

The way it works is that the “tblLogin” table is checked for the existence of the user entered into the form. If a user record is found, they can proceed.

THIS FORM IS LOADED AS A DIALOG FORM WHEN THE DATABASE IS OPENED:

Once that form is passed, the user goes to the main user form, “frmMain”

The administrators form is called “frmAdmin”

Here the admin can add and modify user access rights to the forms and reports.

Here is the code:

Private Sub lblPolicy_Click()
    DoCmd.OpenTable "tblAdmin"
End Sub

Private Sub lblRegister_Click()
    DoCmd.OpenForm "frmRegister"

End Sub


Private Sub lblCurrent_Click()
    DoCmd.OpenTable "tblLogin"
End Sub

Private Sub lblPolicy_Click()

DoCmd.OpenTable "tblAdmin"

End Sub

Private Sub lblRegister_Click()

DoCmd.OpenForm "frmRegister"

End Sub

Private Sub lblCurrent_Click()

DoCmd.OpenTable "tblLogin"

End Sub

This form will open another form called “frmRegister”, which will allow you to enter new users:

This is the code that executes when you click the “Register” button:

Private Sub btnclose_Click()
    DoCmd.Close acForm, Me.Name
End Sub

'=================
'frmRegister code:
'=================

Private Sub btnOK_Click()
 
    Dim strNewUser As String
    Dim strPassword As String
    Dim strSQLInsert As String
    
    strNewUser = Me.txtUser
    strPassword = Me.txtPassword
    
    strSQLInsert = "INSERT INTO tblLogin (UserName,UserPWD) VALUES ('" & strNewUser & "','" & strPassword & "')"
    
    CurrentDb.Execute strSQLInsert
    
    MsgBox "Thanks for registering!"
    
    DoCmd.Close acForm, Me.Name

    
End Sub


Private Sub txtPassword_AfterUpdate()
     Me.lblPassword.Caption = Me.txtPassword
     
End Sub