Microsoft KB Archive/210319

= ACC2000: How to Trap ODBC Logon Error Messages =

Article ID: 210319

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210319



This article applies only to a Microsoft Access database (.mdb).

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



SUMMARY
When you try to log on to Microsoft SQL Server through ODBC by using Visual Basic for Applications, the code that is typically used to trap run-time errors does not trap ODBC logon errors. Instead, you encounter the actual ODBC connection failure error messages.



MORE INFORMATION
The following steps illustrate the approach that is typically used to programmatically trap run-time errors when you are using Visual Basic for Applications:   Open any Access 2000 database, and then type or paste the following function in a new module:NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Option Compare Database Option Explicit

Function fncLoginError(strUserID As String, strPassword As String) On Error GoTo LoginError

Dim wks As DAO.Workspace Dim dbs As DAO.Database Dim con As String

'Replace  with the actual DSN that points to your SQL Server. con = "ODBC;DSN=;UID=" & strUserID & ";PWD=" & strPassword & _ ";DATABASE=Pubs" Set wks = DBEngine.Workspaces(0) Set dbs = wks.OpenDatabase("", False, False, con)

dbs.Close

Exit Function

LoginError: MsgBox "An error has occurred." MsgBox Err.Description Exit Function End Function   Type the following line in the Immediate window, replacing  with a valid SQL Server login ID, replacing  with an invalid password, and then press ENTER: ?fncLoginError("","") Note that you do not reach the error trap unless you cancel the logon attempt, at which point, the error message is:

Operation canceled by user.

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

NOTE: This workaround does not apply to other back-end data sources, such as Oracle. Function fncTestLoginError(strUserID, strPassword) On Error GoTo TestError

Dim dbs As DAO.Database Dim qdf As DAO.QueryDef

Set dbs = CurrentDb Set qdf = dbs.CreateQueryDef("")

'Replace <DSN> with the actual DSN that points to your SQL Server. qdf.Connect = "ODBC;DSN=<DSN>;UID=" & strUserID & ";PWD=" & _ strPassword & ";DATABASE=pubs" qdf.ReturnsRecords = False

'Any SQL statement will work below. qdf.SQL = "SELECT * FROM Authors" qdf.Execute

Exit Function

TestError: MsgBox "An error has occurred." MsgBox Err.Description Exit Function End Function </li>  Type the following line in the Immediate window, replacing <UserID> with a valid SQL Server login ID, replacing <WrongPwd> with an invalid password, and then press ENTER: ?fncTestLoginError("<UserID>","<WrongPwd>") Note that you now reach the error trap when you try to log on and may encounter an error message similar to:

ODBC--connection '<DSN>' failed.

You can use a variation of this function with any form that requires an ODBC logon ID and password. Before logging the user on, 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. </li></ol>

Additional query words: run time runtime

Keywords: kbprogramming kbprb kbusage KB210319

-

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

© Microsoft Corporation. All rights reserved.