ACCESS 95/97 BUG/FEATURE, CAN YOU HELP ?
Re: using forms and error handling with ODBC
Quote:
> All Access 95 would return was 'Error 3146 - ODBC FAILED' when SQL Server
> produced an error.
You are 100% correct in your findings - it is a "Feature" :-) or
limitation when using bound forms to an ODBC SQL Server DSN, you can
not get more information than the error you reference. I'm sure
you're aware that in Access 95/97 there is a new errors collection,
that if your recordset is handled through code (DAO/VBA) it is
possible to enumerate the Errors collection to get the additional
information. Following my signature, there are two articles you may
find
of interest.
Steve
From the 'Microsoft Jet database Engine Programmers Guide'
Error Objects
As you perform operations by using DAO, errors may occur. Each error
is stored as an Error object in the Errors collection. You can use
information contained in the Error object to determine what caused the
error, and to display meaningful error messages to your users.
It's important to note that as each DAO error occurs, the Errors
collection is cleared of previous errors, and the new Error object is
placed in the Errors collection. There can be several related Error
objects in the collection caused by a single operation.
From Technet KB:
HOWTO: Get More Information on the ODBC Call Failed Error
PSS ID Number: Q161288
Article last modified on 07-14-1997
PSS database name: VBWIN
5.00
WINDOWS
======================================================================
---------------------------------------------------------------------
The information in this article applies to:
- Microsoft Visual Basic Professional and Enterprise Editions,
for Windows, version 5.0
---------------------------------------------------------------------
SUMMARY
=======
This article describes how to get more information on the ODBC Call
Failed
error. When errors occur using ODBC databases, Visual Basic will
provide an
"ODBC Call Failed" error message. This generic error message provides
no
specific detail so you must cycle through the Errors collection to get
additional information. Below is a code sample that shows the
difference in
behavior.
MORE INFORMATION
================
The DBEngine has an Errors collection that can be manipulated by the
FOR-
EACH construct. The JET Engine can store multiple errors in the
DBEngine
Errors collection. In Visual Basic 3.0, it was possible to parse the
string
using the routine shown on Page 175 of the Visual Basic 4.0
Professional
Features Book under the "Guide to Data Access Objects" section. The #
symbol was used to separate the "ODBC Call Failed" message from the
detailed ODBC description in Visual Basic 3.0. However, this is not
necessary under Visual Basic versions 4.0 and 5.0.
For the example below, a two-field table called MyTable has been set
up on
an ODBC Source and a primary key set on the ID Field. Two records have
been
added as below:
Field ID Description
===============================
Record 1 1 Hello
Record 2 2 World
The code below will generate an error by trying to add a record with a
duplicate primary key value to test the code:
1. Start a new Standard EXE project. Form1 is added by default.
2. Add a CommandButton to Form1.
3. Add the following code to the General Declarations section of
Form1:
Option Explicit
Private Sub Command1_Click()
Dim db As Database
Dim rs As Recordset
On Error GoTo trap
Set db = OpenDatabase("")
Set rs = db.OpenRecordset("Select * from MyTable")
rs.AddNew
rs.Fields(0).Value = 2
rs.Update
Exit Sub
trap:
MsgBox Errors.Count
MsgBox Err.Number & " " & Err.Description
End Sub
4. Press the F5 key to run the project. Click on the CommandButton and
you
should receive error 3146, "ODBC Call Failed." Although the Error
count
is greater than one, only one message will be displayed.
5. Remove the code from within the error trap and replace it with one
of the following error handlers:
' DAO Error Handler
Dim MyError As Error
MsgBox Errors.Count
For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " " & .Description
End With
Next MyError
' RDO Error Handler
Dim MyError As rdoError
MsgBox rdoErrors.Count
For Each MyError In rdoEngine.rdoErrors
With MyError
MsgBox .Number & " " & .Description
End With
Next MyError
6. Press the F5 key to run the project. You should see a detailed
message
and then the 3146 Error for "ODBC Call Failed."
REFERENCES
==========
Visual Basic 4.0 Professional Features, Chapter 9 of the "Guide to
Data
Access Object"
Jet Database Engine Programmers Guide, pages 425-427
For additional information, please see the following article in the
Microsoft Knowledge Base:
ARTICLE-ID: Q120763
TITLE : How to Retrieve Info from RAISERROR Function in SQL
Server DB
Additional query words: errors Collection
======================================================================
Keywords : kbusage vb5all vb5howto VBKBError VBKBODBC
kbhowto
Version : 5.00
Platform : WINDOWS
Issue type : kbhowto
============================================================================
=
Copyright Microsoft Corporation 1997.
