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

MORE INFORMATION

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.