If you are using a DSN with a userid and password or a DSN-less connection
with windows authentication, you may be able to use something like the
following.
I haven't tested this with a DSN and using windows authentication. I suspect
that the connect string would look like
strConnect = "ODBC;DSN=CMSH;DATABASE=CMSH;Trusted_Connection=Yes"
I haven't yet tested this with a DSN-Less connection and SQL Server
Authentication. Until I do I won't speculate on the connection string needed
to test the connection.
Public Function fTestLoginError() As Long
Author: John Spencer
Purpose: Test MS SQL Server Connection (or Login)
Dim strConnect As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
On Error GoTo Proc_Error
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")
'============================================================================
'DSN-Less Trusted connection - grab the connect string
'from one of the linked tables
'============================================================================
strConnect = dbs.TableDefs("tbl_Actions").Connect
'============================================================================
'DSN connection use a string like the following
' - no quotes around any of the parameters
'strConnect = "ODBC;DSN=CMSH;UID=Wandw;PWD=maskirovka;DATABASE=CMSH"
'============================================================================
qdf.Connect = strConnect 'Set the connect string
qdf.ReturnsRecords = False
'Any SQL valid statement will work below.
qdf.SQL = "SELECT * FROM tbl_Actions"
qdf.Execute
fTestLoginError = 0 'No error encountered
Proc_Exit:
On Error Resume Next
Set qdf = Nothing
Exit Function
Proc_Error:
Select Case Err.Number
Case 3146, 3151
'3146 Call failed
'3151 Connection to SQL Server failed
Case Else
MsgBox Err.Number & ": " & Err.Description, , "fTestLoginError"
End Select
fTestLoginError = Err.Number
Resume Proc_Exit
End Function
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
