ACC: How to Trap Specific ODBC Error Messages (95/97)
ID: Q183278
|
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
Advanced: Requires expert coding, interoperability, and multiuser skills.
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.
- Start Microsoft Access and open the database containing the table that is linked to dbo.authors in the Pubs sample database.
- 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
- Press CTRL+G to open the Debug window.
- 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.
- 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
For more information about the Errors collection, search the Help Index for
"Errors Collection" and display the topic "Errors Collection (DAO)."
Additional query words:
how to raiserror
Keywords : kbdta kbdtacode PgmErr
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto