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:
http://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

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<  I need to convert this to a Class Module that can be use to capture all Errors in all Functions, etc (VBA).>>

 Just to be clear; there's no simple way to do that.   You need to call a procedure (or instantiate the class) from each of your error handlers.

 There are tools that can help with that (MZTools), which will insert error handling in every procedure.

 Other then that, it's vbaWatchDog if you want to drop something in that covers everything.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.