ACC2000: How to Trap ODBC Logon Error Messages (210319)

MORE INFORMATION

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.