Access VBA Database – Open, Connect, Login, & More

This tutorial will demonstrate how to use VBA to open an Access Database. It will also demonstrate how to create a login to access the database – checking if the user name and password exists on database’s tblUsers table.

Open Access Database

This VBA function will open an Access Database:

Public Function OpenAccessDatabase(strDBPath As String)
    If Not IsNull(strDBPath) Then Shell "MSACCESS.EXE """ & strDBPath & """", vbNormalFocus
End Function

You can call the function like this:

Private Sub OpenAccessDatabase_Example()
    Call OpenAccessDatabase("C:\temp\Database1.accdb")
End Sub

Connect to Access Database

Alternatively, you can use this code that will create a new instance of Access, open a database, and assign the database to a variable db:

    Dim objAccess As Access.Application
    Dim db As DAO.Database
    
    Set objAccess = New Access.Application
    Set db = objAccess.DBEngine.OpenDatabase(strDBPath, False, False)

or use this function, containing the above code to open a database to a variable:

Public Function Connect_To_AccessDB(strDBPath As String) As DAO.Database
    Dim objAccess As Access.Application
    Dim db As DAO.Database
    
    Set objAccess = New Access.Application
    Set db = objAccess.DBEngine.OpenDatabase(strDBPath, False, False)
    Set Connect_To_AccessDB = db
End Function

You can call the function and interact with the database using a procedure like this:

Private Sub Connect_To_AccessDB_Example()
    Dim AccessDB As DAO.Database
    
    'Example to assign a database to a variable
    Set AccessDB = Connect_To_AccessDB("c:\temp\TestDB.accdb")

    AccessDB.Execute ("create table tbl_test3 (num number,firstname char, lastname char)")
    
    'Example to assign a close an external database
    AccessDB.Close
    Set AccessDB = Nothing
    
    'Example to delete an external database file (.accdb)
    'Kill ("c:\temp\TestDB.accdb")
    
    'Example to close Access
    'DoCmd.Quit
End Sub

Access Database Login

This VBA function utilizes a login by checking an entered Username and Password against the table tblUsers. In order for this function to work, you will need to create a table tblUsers with fields Password and Username.

Public Function UserLogin(UserName As String, Password As String)
'Check If the user exist in the current database's tblUsers table.
    Dim CheckInCurrentDatabase As Boolean
    CheckInCurrentDatabase = True

    If Nz(UserName, "") = "" Then
        MsgBox "You must enter the Username.", vbInformation
        Exit Function
    ElseIf Nz(Password, "") = "" Then
        MsgBox "You must enter the Password.", vbInformation
        Exit Function
    End If

    If CheckInCurrentDatabase = True Then
        'Verify user credentials
        If Nz(DCount("UserName", "tblUsers", "[UserName] = '" & Nz(UserName, "") & "'"), 0) = 0 Then
            MsgBox "Invalid Username!", vbExclamation
            Exit Function
        ElseIf Nz(Password, "") <> Nz(DLookup("Password", "tblUsers", "[UserName] = '" & Nz(UserName, "") & "'"), "") Then
            MsgBox "Invalid Password!", vbExclamation
            Exit Function
        ElseIf DCount("UserName", "tblUsers", "[UserName] = '" & Nz(UserName, "") & "'") > 0 Then
            Dim strPW As String
            strPW = Nz(DLookup("Password", "tblUsers", "[UserName] = '" & Nz(UserName, "") & "'"), "")
            If Nz(Password, "") = strPW Then
                'Set Username and Password as Global Veriables
                TempVars.Add "CurrentUserName", Nz(UserName, "")
                TempVars.Add "CurrentUserPassword", Nz(Password, "")
                MsgBox "Logged in successfully", vbExclamation
            End If
        End If
    Else
        'Set Username and Password as Global Veriables
        TempVars.Add "CurrentUserName", Nz(UserName, "")
        TempVars.Add "CurrentUserPassword", Nz(Password, "")
       MsgBox "Logged in successfully", vbExclamation
    End If

End Function

You can call the function like this:

Private Sub UserLogin_Example()
    Call VBA_Access_General.UserLogin("Username", "password")
End Sub