ACC: How to Trap Specific ODBC Error Messages (95/97)

• Microsoft Access versions 7.0, 97

SUMMARY

You can use the Errors collection to trap specific 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. More specific information returned by the ODBC data source is available in subsequent members of the collection.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

The following example demonstrates a Visual Basic for Applications procedure that 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 SQL Server sample database named Pubs.

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 enter the following procedure:




      Function TestODBCErr(strTableName As String)

         On Error GoTo ODBCErrHandler

         Dim db As Database
         Dim rs As 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 Error

         If Errors.Count > 1 Then
            For Each errX In 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 open the Debug window.



4. Type the following line in the Debug window, and then press ENTER:

   ?TestODBCErr("dbo_authors")

   Note that the function returns the number of the generic "ODBC Call--failed" error, but it also returns detailed error information from the ODBC data source itself.



5. Type the following line in the Debug window, and then press ENTER:

   ?TestODBCErr("xxxx")

   Note that you receive the expected Visual Basic for Applications error indicating that the Microsoft Jet database engine could not find the input table.

REFERENCES

For more information about trapping ODBC errors in Microsoft Access 2.0, please see the following article in the Microsoft Knowledge Base:

Q129165 ACC2: Trapping Specific ODBC Error Messages

Additional query words: how to raiserror

Keywords          : kbdta kbdtacode PgmErr 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: August 3, 1999