MS Access Object Security

Today’s post is based on a question that I received.

We are going to assume that there are multiple users who have rights
to view different database objects.

So in this example there are 3 user logins with different rights to access 3 forms and 3 reports.

Here is an overview of the database setup:

This is tblLogins:

This is tblAdmin:

Notice that all you really need to do is check whether the user has access to the object.

If you want, you can assign the permissions based on groups, instead of user by user. Then you can assign the users to groups, and the groups can have the permissions to the objects.

On the “Open” event for each form, place the following code:

Private Sub Form_Open(Cancel As Integer)
    If Not Allowed(Me.Name) Then
        MsgBox "You are not allowed here"
        Cancel = True
    End If
End Sub

On the “Open” event for each report, place the following code:

Private Sub Report_Open(Cancel As Integer)
    If Not Allowed(Me.Name) Then
        MsgBox "You are not allowed here"
        Cancel = True
    End If
End Sub

On the “Open” event of each database object we are using the “Allowed” function to let us know if the user is allowed to open the database object.

The “Allowed” function is used by a number of forms and reports, so we put the “Allowed” function in a module called “basSecurity” by itself.

Here is the “Allowed” function:

Public Function Allowed(ObjectName) As Boolean
    Dim rst As Recordset
    Dim strSQL As String

    
    strSQL = "SELECT Allowed FROM tblAdmin WHERE ObjectName='" & ObjectName & "' AND UserName ='" & m_LoggedInUser & "'"
        
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    If rst(0) = True Then
        Allowed = True
    Else
        Allowed = False
    End If
    
    rst.Close
    Set rst = Nothing
    
End Function

This function will check the tblAdmin table to see if the user is allowed to open the requested object.

basSecurity also has a modular scope variable called “m_LoggedInUser”, to store the user name of the currently logged in user.

Here is the screenshot all together:

To test and open all the forms and reports I am using a form called “frmMain”:

…and here is “behind the scenes”:

That’s about all, let me know if you have any questions.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!