Excel VBA Error handling On Access Database Connection

Question

I'm trying to create an error handler that alerts user when connection fails. Everything seem to go well in the code till the point of ending the sub, i get an error message "Object Variable or with block variable not set"

i checked the objects i defined and tried setting them to nothing yet.. Also, in other to initiate the error, i've deliberately turned off the network on my Computer to run this code. So basically code begins from the start of the error handler to where it points to.

below is the code:

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim qry As String
    
    '''connection and query the database
    
        Dim dbOpen As String
    
        dbOpen = "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=\\server-pc\Users\Administrator\Documents\NCMS Database\NcmsDatabase1.accdb;PERSIST SECURITY INFO=FALSE;Jet OLEDB:System database=C:\Users\user\AppData\Roaming\Microsoft\Access\System.mdw"
    
        qry = "SELECT * FROM tbl_UserAccount"
           
    On Error GoTo CloseConnection:
    cnn.Open dbOpen
    
    On Error GoTo CloseRecordset:
    rst.Open qry, cnn, adOpenDynamic, adLockPessimistic
        
    Dim i As Integer
    Dim n As Long
    
    Dim Sh As Worksheet
    Set Sh = ThisWorkbook.Sheets("UserAccount")
    
    Sh.Cells.ClearContents
        
    '''COpy from database to helper sheet and get the headers
    Sh.Range("A2").CopyFromRecordset rst
    
    For i = 1 To rst.Fields.Count
        Sh.Cells(1, i).Value = rst.Fields(i - 1).Name
    Next i
    
 CloseRecordset:
    rst.Close
    
 CloseConnection:            
        If Not (cnn Is Nothing) Then
            If (cnn.State And adStateOpen) = adStateOpen Then cnn.Close
            MsgBox "Kindly check server connection", vbCritical + vbOKOnly, "Error Alert"
            Unload Me
    '        If Err.Number <> 1 Then
    '            On Error GoTo 0
    '        End If
        End If
        
    'Set rst = Nothing
    'Set cnn = Nothing
End Sub

Kindly Help

Answer 1

I found out the Code Unload.me cannot run while the Userform initializes, so basically i ran the code on Userform Activate Event and it works fine. i omitted to mention the event my code was running on, my apologies!