KB124901 - ACC: How to Trap ODBC Login Error Messages

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.

MORE INFORMATION

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."

ACC: How to Trap ODBC Login Error Messages

SYMPTOMS

RESOLUTION

MORE INFORMATION

Steps to Reproduce Behavior