Processing Error Information from Oracle Back-End

[whdyck]

I'm using MS Access 2003 with an Oracle back-end via ODBC.

I have Oracle triggers that raise various error numbers on the back end, like this:

EXCEPTION
  WHEN billing_details_exist THEN
    raise_application_error(-20000, 'Billing details exist for the rate period for rate type ' || cRateTypeWithBillingDetails || '. Change or Delete not permitted.');
  WHEN OTHERS THEN
    raise_application_error(-20020, SQLERRM);
END;

When MS Access sees this error, I'd like to be able to extract the error number (-20000) and the corresponding message ('Billing details exist ...')

Anyone know how to do this, or if it's even possible?

Thanks!

Wayne

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=220309
This seems to be an ongoing question since the ODBC (including SQL Server Native Client) is a shell that doesn't seem to support VBA as well as it supports MS preferred language. Some might say it is a kind of punishment since the information is possible to be available.


Found something new, it looks interesting will post it as is. If you have time to test it, feedback would be welcome:
Usually the DBEngine Errors will give you a much more understandable and precise error message when ODBC errors are concerned than the VBA Error you normally get. You can loop through the DBEngine Errors collection to get the information. You can download a code module (it is a zipped file) from my site that you can import and use in your applications. It also makes your error handling code in your procedures shorter. The error message is broken up into segments and and you can remove segments you do not want to use just by commenting out the appropriate lines of code. And there is an alternative to save error messages in a table which has saved my neck a few times with remote users. I have fixed it now but in the download the example to call the procedure should be

' Example : MsgBox PAWErrors("MyProcedure of Module MyModule")

Attribute VB_Name = "modPAWErrors"
Option Compare Database
Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : PAWErrors
' Author    : Patrick Wood  http://gainingaccess.net
' Date      : 9/24/2011
' Purpose   : Loops through all DBEngine.Errors and gets VBA Errors
' Argument  : strProc is the Procedure in which the error occurred
' Example   : MsgBox PAWErrors("MyProcedure of Module MyModule")
'---------------------------------------------------------------------------------------
'
Public Function PAWErrors(strProc As String) As String

    Dim dbErr As DAO.Database
    Dim i As Long
    Dim strErr As String
    Dim strDBErr As String
    
    If Errors.Count > 1 Then
        With DBEngine.Errors
            For i = 0 To .Count - 1
                'Skip the "dummy" Error
                If .Item(i).Description <> "Could not find file 'NoDB'." Then
                    'If Error Number same as VBA Error let VBA Error manage it
                    If .Item(i).Number <> Err.Number Then
                        'Build the DBEngine Error String
                        strDBErr = strDBErr & "DBEngine Error Number: " & .Item(i).Number & vbCrLf
                        strDBErr = strDBErr & "Description: " & .Item(i).Description & vbCrLf
                        strDBErr = strDBErr & "In Procedure " & strProc & vbCrLf
                        'These last three lines are optional - To skip them make them comments
                        strDBErr = strDBErr & "Error Source: " & .Item(i).Source & vbCrLf
'                        strDBErr = strDBErr & "HelpContext: " & .Item(i).HelpContext & vbCrLf
                        strDBErr = strDBErr & "HelpFile: " & .Item(i).HelpFile & vbCrLf & vbCrLf
                    End If
                End If
'                Debug.Print "DBEngine Errors: " & vbCr & strDBErr & vbCr
            Next i
        End With
    End If

    'Get the VBA Error information
    'There is always only one VBA Error
    strErr = strErr & "VBA Error Number: " & Err.Number
    strErr = strErr & vbCrLf & Err.Description
    strErr = strErr & vbCrLf & "In Procedure " & strProc
    'The next four lines are optional and can be commented out
    strErr = strErr & vbCrLf & "Error Source: " & Err.Source
'    strErr = strErr & vbCrLf & "HelpContext: " & Err.HelpContext
    strErr = strErr & vbCrLf & "HelpFile: " & Err.HelpFile
    'The Error Date and Time is useful when saving the Error
    'information in a table or sending it as an email
    strErr = strErr & vbCrLf & "Error Date and Time: " _
        & Format(Now(), "yyyy-mm-dd hh:nn:ss AMPM")
    
    'Add the DBEngine Errors to the Error String
    strErr = strDBErr & strErr
    
    'Optional-Comment out if not needed
	'Save the Error Information in a Table
    Call SaveErrorInfo(strErr)
    
    'Create a "dummy" error as a Flag
    'DBEngine Errors never removes the last Error in
    'the collection. If no DBEngine error has occurred
    'it will still return the last error.
    'Create a "dummy" error as a flag and skip that error.
    'Trigger the "dummy" DBEngine error.
    If Len(strDBErr & "") > 0 Then
        On Error Resume Next
        Set dbErr = OpenDatabase("NoDB")
    End If

    'Return Error Message
    PAWErrors = strErr
    
End Function

'---------------------------------------------------------------------------------------
' Procedure : SaveErrorInfo
' Author    : Patrick Wood - Gaining Access Technologies http://gainingaccess.net/
' Date      : 3/23/2012
' Purpose   : Saves Error Info in tblErrorRecords Table
'---------------------------------------------------------------------------------------
'Table Fields		Data Types		Size
'ErrorMessageID 	Autonumber or Long Integer
'ErrorMessage		Memo
'ErrorDateTime		Date/Time
'ErrorUser			Text			50
'ErrorPC			Text			50

Private Function SaveErrorInfo(strErr As String) As Boolean
On Error GoTo ErrHandle

    Dim db As DAO.Database
    Dim strSQL As String    'strSQL
    Dim dteNow As Date      'ErrorDateTime
    Dim strUser As String   'ErrorUser
    Dim strPCName As String 'ErrorComputerName
    Dim lngID As Long       'ErrorMessageID
          
    strUser = Environ("USERNAME")
    strPCName = Environ("COMPUTERNAME")
    dteNow = Now()
    'This table does not use AutoNumber - we increment the ID ourselves
    lngID = Nz(DMax("ErrorMessageID", "tblErrorRecords"), 0) + 1
    
    strSQL = "INSERT INTO tblErrorRecords" _
            & "(ErrorMessageID, ErrorMessage, ErrorDateTime, ErrorUser, ErrorPC)" _
            & " Values (" & lngID & ",""" & strErr & """, #" & dteNow & "#, " _
            & """" & strUser & """, """ & strPCName & """);"

    Set db = CurrentDb
    
    db.Execute strSQL, dbFailOnError
    
    'Keep no more than 100 records in the table
    'Delete the first 50 records if over 100 records exist
    If DCount("*", "tblErrorRecords") > 100 Then
        lngID = DMax("ErrorMessageID", "tblErrorRecords")
        lngID = lngID - 50
        strSQL = "DELETE * FROM tblErrorRecords" _
            & " WHERE ErrorMessageID < " & lngID & ";"
            
        db.Execute strSQL, dbFailOnError
    End If
    
    If Err.Number = 0 Then
        SaveErrorInfo = True
    End If
    
    Set db = Nothing

ExitHere:
    Exit Function

ErrHandle:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")" _
    & vbCrLf & "In Procedure SaveErrorInfo of modPAWUtilities"
    Resume ExitHere
            
End Function