Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Trap ODBC Logon Error Messages

ACC2000: How to Trap ODBC Logon Error Messages

View products that this article applies to.

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.

↑ Back to the top

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:
  1. 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 <DSN> with the actual DSN that points to your SQL Server.
    con = "ODBC;DSN=<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
  2. 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:
    ?fncLoginError("<UserID>","<WrongPwd>")
    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.
  3. 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
  4. 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.

↑ Back to the top

Keywords: KB210319, kbusage, kbprb, kbprogramming

↑ Back to the top

Article Info
Article ID : 210319
Revision : 3
Created on : 1/26/2005
Published on : 1/26/2005
Exists online : False
Views : 351