Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America asked on

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/Microsoft/Development/MS_Access/Q_28226629.html#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

Open in new window

Microsoft SQL ServerMicrosoft AccessMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Karen Schaefer

8/22/2022 - Mon
Jim Dettman (EE MVE)

No need for it to be a class module....you simply can call the procedure from each of your error handlers.

If your looking for some slick error handling, take a look at vbaWatchDog:

http://www.everythingaccess.com/vbwatchdog/installation.htm

It hooks right into VBA and provides global error handling.

Jim.
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Karen Schaefer

Thanks for the suggestions, however, it turns out that i was not suppose to create just Error handle, but an event/process log.   So I still need to create the Class Modules and call theses modules within the various code to capture the actions of the code.  please see my new post asking for help with incorporating the Class/Functions within the code.
Your help has saved me hundreds of hours of internet surfing.
fblack61