ACC2000: How to Trap Specific ODBC Error Messages

Article translations Article translations
Article ID: 209855 - View products that this article applies to.
This article was previously published under Q209855
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

SUMMARY

You can use the Errors collection to trap specific Open Database Connectivity (ODBC) errors. However, you must loop through all of the elements in the collection to access the ODBC error information. The first element contains only a number and description for a generic "ODBC Call--failed" error message. More specific information returned by the ODBC data source is available in subsequent members of the collection.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following sample Microsoft Visual Basic for Applications procedure loops through the Errors collection to access error information returned from an ODBC data source. It assumes that you have linked the dbo.authors table to your database from the Microsoft SQL Server sample database named Pubs.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

  1. Start Microsoft Access and open the database containing the table that is linked to dbo.authors in the Pubs sample database.
  2. Create a new module and type the following procedure:
    Function TestODBCErr(strTableName As String)
    
       On Error GoTo ODBCErrHandler
    
       Dim db As DAO.Database
       Dim rs As DAO.Recordset
       Set db = CurrentDb()
       Set rs = db.OpenRecordset(strTableName, dbOpenDynaset)
       With rs
          .AddNew
          ![au_id] = "999-88-7777"
          ![au_fname] = "Jane"
          ![au_lname] = "Doe"
          ' Note you do not supply data for the required field contract.
          .Update
          .Close
       End With
    
    Exit_function:
       Exit Function
    
    ODBCErrHandler:
       Dim errX As DAO.Error
    
       If Errors.Count > 1 Then
          For Each errX In DAO.Errors
             Debug.Print "ODBC Error"
             Debug.Print errX.Number
             Debug.Print errX.Description
          Next errX
       Else
          Debug.Print "VBA Error"
          Debug.Print Err.Number
          Debug.Print Err.Description
       End If
       Resume Exit_function
    End Function
    					
  3. Press CTRL+G to go to the Immediate window.
  4. Type the following line in the Immediate window, and then press ENTER:
    ?TestODBCErr("dbo_authors")
    Note that the function returns the number of the generic "ODBC Call--failed" error message, but it also returns detailed error information from the ODBC data source itself.

  5. Type the following line in the Immediate window, and then press ENTER:
    ?TestODBCErr("xxxx")
    Note that you receive the following Visual Basic for Applications error message:
    The Microsoft Jet database engine cannot find the input table or query 'xxxx'. Make sure it exists and that its name is spelled correctly.

REFERENCES

For more information about the Errors collection, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type errors collection in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 209855 - Last Review: June 23, 2005 - Revision: 3.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbinfo kbdtacode KB209855

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com