Public objConn As New ADODB.Connection

Sub DBConnectionAccess()

    If CBool(objConn.State And adStateOpen) Then objConn.Close

    Dim strPathToDB As String
                
    On Error GoTo ErrHandler:
            
    'open connection to Access database
    strPathToDB = Range("PathToDB").Value + "VolMan.accdb"             '"C:\Shana - NHS\Database\VolMan.accdb"
    objConn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPathToDB

    'MsgBox "Connection is Ok"
'    MsgBox strPathToDB
    
    Exit Sub
    
ErrHandler:
    ' error handling code
    MsgBox "No connection!" & vbCrLf & "Check whether you have the correct path."

End Sub

Sub DBConnectionClose()

    If CBool(objConn.State And adStateOpen) Then objConn.Close
    Set objConn = Nothing

End Sub