ACC: How to Trap ODBC Login Error Messages (124901)
The information in this article applies to:
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
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.
Modification Type: | Minor | Last Reviewed: | 10/11/2006 |
---|
Keywords: | kbprb kbProgramming kbusage KB124901 |
---|
|