Microsoft KB Archive/124901

= ACC: How to Trap ODBC Login Error Messages =

Article ID: 124901

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q124901



Advanced: Requires expert coding, interoperability, and multiuser skills.



SYMPTOMS
When you try to log in to Microsoft SQL Server through ODBC by using Visual Basic for Applications code, the code that seems most direct does not trap ODBC login errors. Instead, it returns a connection failure error message.

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.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0



RESOLUTION
To work around this behavior and supply your own error message in place of the error returned by ODBC, you can use the following sample code. This code tests the connection by trying to run an SQL pass-through query, which uses a different method of connecting and is able to trap errors. This sample code uses Microsoft SQL Server as the ODBC data source.

NOTE: This workaround specifically does not work with ORACLE.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. Function Test_Login_Error (UserID, Password)

On Error GoTo Error_Trap

Dim mydb As Database Dim myq As QueryDef Set mydb = CurrentDB Set myq = mydb.CreateQueryDef("") myq.connect = "ODBC;DSN=opus;UID=" & UserID & ";PWD=" & _ Password &";LANGUAGE=us_english;DATABASE=pubs"

myq.returnsrecords = False

' Any SQL statement will work below. myq.sql = "select * from authors" myq.Execute Exit function

Error_Trap: MsgBox "An error has occurred." MsgBox Error Exit Function

End Function You can use a variation of this function with any form that requires an ODBC login ID and password. Before logging the user in, use the code to test the user's ID and password on the ODBC data source. Note that testing with this function does not consume extra connections. When you reconnect to the same data source, the same connection is used.



Steps to Reproduce Behavior
  Create the following function in a module: Function Login_Error (UserID, Password)

On Error GoTo Error_Trap2

Dim myws As WorkSpace, connstr As String Dim mydb As Database

connstr = "ODBC;DSN=opus;UID=" & UserID & ";PWD=" & _ Password & ";LANGUAGE=us_english;DATABASE=pubs"

Set myws = DBEngine.Workspaces(0) Set mydb = myws.OpenDatabase("", False, False, connstr)

mydb.Close

Exit Function

Error_Trap2: MsgBox "An error has occurred." MsgBox Error Exit Function

End Function  Type the following line in the Debug window (or Immediate window in version 2.0), and then press ENTER:

?Login_Error("myuser","wrongpassword")

Note that you do not reach the error trap unless you cancel the login attempt, at which point the error message is "Operation canceled by user."

Keywords: kbprb kbprogramming kbusage KB124901

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.