As part of Securing your Database I mention:

I have had a couple inquiries about this and thought today I’d explain the process.

There are basically two, very similar solutions. Both approaches involve using a hidden form (you could also do this using an AutoExec macro) that you set as your database’s ‘Display Form’ or automatically open as part of your startup routine.

Hard Coded Authorized Users

As its name indicates, this approach involves simply entering the name(s) of the authorized user(s) directly in the code.

As part of the hidden form’s open event you simply do:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

    Select Case CreateObject("WScript.Network").UserName
        Case "Daniel", "John" 'This is the list of authorized usernames
            'Do something
            '   Open your menu, default form, ...
        Case Else
            MsgBox "You are not authorized to use this database.", vbInformation Or vbOKOnly, "Shutting down"
            Application.Quit
    End Select
    
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Form_Open" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

This can be a solution for a single user database, such as your own private tool, but quickly becomes a pain to manage when using in a corporate environment where users can come and go. This is where the second approach, Table Driven Authorized Users can ease things.

Table Driven Authorized Users

As its name indicates, in this setup, we first need to create a users table which needs to include a username field that we can then query to validate the current user against.

So the first we need to do is create a ‘Users’ table and populate it with the authorized usernames.

CREATE TABLE Users 
   (
      UserId AUTOINCREMENT PRIMARY KEY,
      UserName VARCHAR(255) NOT NULL,
      ComputerName VARCHAR(255),
      ComputerIP VARCHAR(20),
      EmailAddress VARCHAR(255)
   );

Now, simply edit the table and enter the (Windows/Domain) usernames of the people you wish to allow to use your database. Obviously, at this point in time you can build a form to simplify data entry …, but I leave that up to you.

Once that is done, then it is simply a question of coding a simple check of the current username against the authorized users table and that can be achieve with

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler
    Dim sUsername As String
    
    'Get the system's currently logged in username
    sUsername = CreateObject("WScript.Network").UserName
    'Lookup to see if that user is in the user table to use this database
    If IsNull(DLookup("UserId", "Users", "username='" & sUsername & "'")) = True Then
        MsgBox "You are not authorized to use this database.", vbInformation Or vbOKOnly, "Shutting down"
        Application.Quit
    End If

    'Do something
    '   Open your menu, default form, ...
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Form_Open" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Final Remarks

Considering the flexibility that the table approach offers and the fact that the code is no more complex I would advise that you simply use this approach. It is versatile, easy to manage, conforms best to proper database setup (using table to manage) and doesn’t require editing hard coded values.

Why use a hidden form? The answer is you don’t have to if you don’t want to, but the hidden form approach can provide you with the ability to house data (username, login time, …) which you can later use throughout your application. This is a typical approach used by many seasoned developers. That said, there is also absolutely nothing wrong in performing the check as part of a standard module Function and calling it as part of a AutoExec macro. As always, the choice is entirely yours.

That said, there are a couple major security issues with Access: is Trusted Locations and the Shift Bypass which mean that a knowledgeable user can stop your startup code (AutoExec), Display Form from loading and walk around such measures. While this, like almost any security measures, can be circumvented, security is a layered process and this is still useful for most scenarios. If you have users going to the lengths of bypassing code at startup, you already have bigger issues at hand.