Create a Class Module to capture Error Handling

Looking for info and direction on how to implement an MS Access -  Class Module that will capture error messages and update an SQL table within Sql Server from Access.

I already have the SPROC and the basic code written - This code does update the SQL tables Data.  I need to convert this to a Class Module that can be use to capture all Errors in all Functions, etc (VBA).

This is what I have so far:  see ee# for prevous post on the SQL update portion of this code:
https://www.experts-exchange.com/questions/28226629/Using-Scope-Identity-with-an-Update-Query.html?anchorAnswerId=39465508#a39465508

what is the best approach?

K

Option Compare Database
Option Explicit

'Compiled in Access 2003, 04-Sept-2013
'REFERENCES NEEDED:
'
'Visual Basic For Applications
'Microsoft Access 11.0 Object Library
'Microsoft DAO 3.6 Object Library
'OLE Automation
'Microsoft ActiveX Data Objects 2.8 Library

Public Const SPROC As String = "ISCenter_Monitor.usp_log_ISCenter_Event"
Public Const ConnStr = _
    "Driver={SQL Server};Server=AQL02;database=TRACI_ANALYTICS;UID=;PWD="


Public Sub ErrorLogger()
    
    Dim EventID As Long
    Dim RowsAffected As Long
    Dim ErrorMessage As String
    Dim AdditionalInfo As String
    Dim StepSucceeded As Integer
    
    On Error GoTo PROC_ERROR
    
    RowsAffected = 999
    ErrorMessage = "Test Error Message #003"
    AdditionalInfo = "Testing from MS Access VBA/ADODB"
    StepSucceeded = 1
    
    EventID = OpenErrorLogger()
    If EventID <> 0 Then UpdateErrorLog EventID, RowsAffected, _
        ErrorMessage, AdditionalInfo, StepSucceeded
    MsgBox "EventID was " + CStr(EventID)
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox Err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    
End Sub

Public Function OpenErrorLogger() As Long
    On Error GoTo PROC_ERROR
    
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim EventID As Long
    
    EventID = 0
    
    conn.ConnectionString = ConnStr
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = SPROC
        .NamedParameters = True
            .Parameters("@EventName").Value = "Test Event"
            .Parameters("@ModuleName").Value = "Test Module"
            .Parameters("@ProcedureName").Value = "Test Procedure"
    End With
    cmd.Execute
    EventID = cmd.Parameters("@RETURN_VALUE").Value
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox Err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    On Error Resume Next
    conn.Close
    OpenErrorLogger = EventID
    
End Function
Public Sub UpdateErrorLog(EventID As Long, RowsAffected As Long, ErrMsg _
    As String, AdditionalInfo As String, StepSucceeded As Integer)
    On Error GoTo PROC_ERROR
   
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    
    conn.ConnectionString = ConnStr
    conn.Open
    
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = SPROC
        .NamedParameters = True
    
        .Parameters("@EventID").Value = EventID
        .Parameters("@ErrorMessage").Value = ErrMsg
        .Parameters("@StepSucceeded").Value = StepSucceeded
        .Parameters("@AffectedRows").Value = RowsAffected
        .Parameters("@AdditionalInfo").Value = AdditionalInfo
    End With
    
    cmd.Execute
    
    GoTo PROC_EXIT
    
PROC_ERROR:
    MsgBox Err.Description
    Resume PROC_EXIT
    
PROC_EXIT:
    On Error Resume Next
    conn.Close
    
End Sub

Select allOpen in new window