Access Database Users And Permissions Example

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

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
        m_LoggedInUser = Me.txtName
        DoCmd.Close acForm, Me.Name
        MsgBox "Welcome: " & m_LoggedInUser
        DoCmd.OpenForm "frmMain"
        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.


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

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

That’s about all. I’ll load the database for you all here below.

Let me know if you need help.


Here is the database access database users and permissions.accdb

Offsite Related Information:

“Go Fund Me” Page

($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Tags: ,