Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-25-2010, 07:06 AM   #1
Djblois
Newly Registered User
 
Join Date: Jan 2009
Posts: 555
Thanks: 0
Thanked 0 Times in 0 Posts
Djblois is on a distinguished road
Trapping Errors: ODBC and ACCESS

Currently I use a Error module to trap errors in access but I have trouble with trapping ODBC errors. I found this code but Where would I put this function so I can trap ODBC errors?


http://support.microsoft.com/kb/209855

Here is my error trapping code:

Code:
    Sub UnknownError(strSub As String, lngErrCode As Integer, strErrDesc As String, Optional strControl As String)
   
      LogError strSub, lngErrCode, strErrDesc, strControl
      DoCmd.OpenForm "frmGeneralError"
      Forms!frmGeneralError.lblError.Caption = strSub & " - " & "Error#" & lngErrCode & ": " & strErrDesc
      End
   
  End Sub
Code:
    Sub LogError(strSub As String, lngErrCode As Integer, strErrDesc As String, Optional strControl As String)
   
      Dim cnn As ADODB.Connection
      Dim strSQL As String
      
      Set cnn = CurrentProject.Connection
      
      
      strSQL = "INSERT INTO tblLog (ErrorNum, ErrMessage, UserName, ErrTime, BuildNum, CurrentSub, CurrentControl) "
      
      strSQL = strSQL & "VALUES ( " & lngErrCode & ", '" & strErrDesc & "', '" & strUserLogin _
          & "', #" & Date & "#, '" & DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1") & _
          Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00") & _
          Format(DLookup("[BuildNo]", "tblVersion", "[VersionID] = 1"), ".00") & "', '" & strSub & "', '" & _
          strControl & "' ) "
          
      cnn.Execute strSQL, , adExecuteNoRecords
   
  End Sub
Plus, if anyone can see any suggestions on how to log the errors better or other information I should be logging please do not hesitate.

Djblois is offline   Reply With Quote
Old 08-25-2010, 07:28 AM   #2
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,319
Thanks: 0
Thanked 69 Times in 60 Posts
Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light
Re: Trapping Errors: ODBC and ACCESS

In general, I write a separate error handling function specifically for ODBC and reference it whenever I have forms that uses ODBC linked tables as recordsource (use Form's OnError event) and for any procedure where I open a recordset against ODBC linked tables. My ODBC error handling function then attempts to differniate between different ODBC errors and where possible, get more information about the error from the backend (easy to do with MySQL, doable with PostgreSQL, not possible with SQL Server for the OnError event) and then return the error information back to the original procedure to decide what to do with it.

HTH.
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 08-25-2010, 07:34 AM   #3
Djblois
Newly Registered User
 
Join Date: Jan 2009
Posts: 555
Thanks: 0
Thanked 0 Times in 0 Posts
Djblois is on a distinguished road
Re: Trapping Errors: ODBC and ACCESS

Sounds interesting would that work with Oracle? and do you have any ideas on how to do it in Oracle?

Djblois is offline   Reply With Quote
Old 08-25-2010, 07:46 AM   #4
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,319
Thanks: 0
Thanked 69 Times in 60 Posts
Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light
Re: Trapping Errors: ODBC and ACCESS

It works with any backends.

The issue I mentioned is explained in this KB article, which as mentioned SQL Server cannot provide. I can do it with MySQL because MySQL has "SHOW ERRORS" which I can call via a passthrough query and thus obtain the detailed information from MySQL and customize my response based on that information. PostgreSQL doesn't have this out of the box, but I can write a stored procedure in PostgreSQL to do basically same thing as MySQL's SHOW ERRORS does.

So, it will depend on whether Oracle has functionality similar to MySQL's SHOW ERRORS that you can call via a passthrough query. For my projects with SQL Server, where this is not available, I just issue a generic message stating different possible causes for the error, which is basically the best I can do.

I hope that helps.
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 08-25-2010, 07:48 AM   #5
Djblois
Newly Registered User
 
Join Date: Jan 2009
Posts: 555
Thanks: 0
Thanked 0 Times in 0 Posts
Djblois is on a distinguished road
Re: Trapping Errors: ODBC and ACCESS

Would you mind posting your error trapping code for both situations so I can have a look. That would be very helpful and I will look at the article as well. Thank you very much.
Djblois is offline   Reply With Quote
Old 08-25-2010, 08:10 AM   #6
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,319
Thanks: 0
Thanked 69 Times in 60 Posts
Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light
Re: Trapping Errors: ODBC and ACCESS

This is for MySQL:

Code:
Option Compare Database
Option Explicit

Static Property Get MySQLErrorMessage(iCode As Integer) As String

   On Error GoTo MySQLErrorMessage_Error

Select Case iCode
    Case 1062
        MySQLErrorMessage = "You tried to insert a duplicate record where duplicates are not permitted. Please remove any duplicate values and retry:"
    Case 1142
    'May be associated with DAO error code 3155, 3156, 3157
        MySQLErrorMessage = "You do not have sufficient privilege to perform the actions. Please contact the adminstrator if you require the privilege:"
    Case 1364
        MySQLErrorMessage = "There are required values that weren't filled in. Please fill in all values:"
    Case Else
        MySQLErrorMessage = ""
End Select

   On Error GoTo 0
   Exit Property

MySQLErrorMessage_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MySQLErrorMessage of Module modError"

End Property

'This function is to be used for any DAO operations
Public Function GetODBCError() As String

Dim derr As DAO.Error

For Each derr In DAO.Errors
    If InStr(derr.Description, "ODBC--Call failed") = 0 Then
        GetODBCError = derr.Number & ";" & derr.Description
    End If
Next

End Function

'This function is to be used for any errors caught by form's OnError event
Public Function TrapODBCError(ErrCode As Integer) As Integer

Dim sErr As String
Dim rst As DAO.Recordset

   On Error GoTo TrapODBCError_Error

Select Case ErrCode
    'Make sure this is an ODBC error
    Case 3146, 3151, 3154 To 3157, 3231 To 3232, 3234 To 3235, 3238, 3247, 3254, 3613
        Set rst = CurrentDb.OpenRecordset("eGetError", dbOpenSnapshot)
        
        With rst
            If Not .BOF And Not .EOF Then
                .MoveFirst
                Do Until .EOF
                    sErr = sErr & MySQLErrorMessage(.Fields(1).Value) & ": "
                    sErr = sErr & .Fields(2).Value & vbCrLf
                    .MoveNext
                Loop
            End If
        End With
        MsgBox sErr, vbCritical + vbOKOnly, "Error!"
        TrapODBCError = acDataErrContinue
    'Technically, this is not a ODBC error, but requires a custom message as
    ' the default message is too confusing for users
    Case 3162
        MsgBox "The value cannot be left blank. Pleaes fill in the required value.", vbInformation + vbOKOnly, "Info required."
        TrapODBCError = acDataErrContinue
    'It's not an ODBC error.
    Case Else
        TrapODBCError = acDataErrDisplay
End Select

Set rst = Nothing

   On Error GoTo 0
   Exit Function

TrapODBCError_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure TrapODBCError of Module modError"

End Function
Note that "eGetError" is just a passthrough query that contains "SHOW ERRORS;" and is sent to MySQL directly. Also note that there are two different processes, depending on where we are getting the errors from, from a DAO operation in VBA code or from form's OnError event.


This is for SQL Server:
Code:
Option Compare Database
Option Explicit

Public Function HandleODBCError(DataErr As Integer, Optional ErrSource As String, Optional ErrDesc As String) As Boolean
On Error GoTo Err_Handler

Select Case DataErr
    Case 2085, 2339, 3146, 3151, 3154, 3155, _
         3156, 3157, 3231, 3232, 3234, 3235, _
         3238, 3247, 3254, 3299, 3423, 3613
    Me.Show 1
    HandleODBCError = True
Case Else
    HandleODBCError = False
End Select

Exit_Procedure:
    On Error Resume Next
    Exit Function
Err_Handler:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & ", " & Err.Description
    End Select
    Resume Exit_Procedure
    Resume
End Function

Private Sub cmdOK_Click()
On Error GoTo Err_Handler

    Me.Hide
    
Exit_Procedure:
    On Error Resume Next
    Exit Sub
Err_Handler:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & ", " & Err.Description
    End Select
    Resume Exit_Procedure
    Resume
End Sub

Private Sub UserForm_Activate()
On Error GoTo Err_Handler

    Me.Caption = AppGlobal.ApplicationNameAndDB()

Exit_Procedure:
    On Error Resume Next
    Exit Sub
Err_Handler:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & ", " & Err.Description
    End Select
    Resume Exit_Procedure
    Resume
End Sub
Which basically displays a form with a message enumerating all possible causes (e.g. you forgot to fill in a required field, some data you put in is not valid, etc. etc.).

Note that the 2nd variant is specifically for form errors only. I handle SQL Server's errors as normal when done via code because they are accessible there.

HTH.

__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
The Following User Says Thank You to Banana For This Useful Post:
vboyes (02-21-2014)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Beginner's Guide to managing ODBC Datasources. Banana General 4 11-27-2007 10:41 AM
Converting ODBC Tables to Access Tables MurrayP Tables 2 10-13-2007 06:32 PM
Microsoft Access ODBC AS400 Connection hiker8117 SQL Server 4 03-10-2007 06:46 AM
Trapping Odbc Connection Errors DALIEN51 General 0 07-29-2005 01:42 AM




All times are GMT -8. The time now is 05:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World