How To Check SQL Connection (DAO)

[Chlaris]

Dear all,

In load event of main menu, I want to check SQL Connection. If it's not
successful, I want to open a dialog box of database connection.
How to check the SQL connection is sucessful or not using DAO ?
Thanks.

Chlaris

 

[John Spencer]

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

 

[Chlaris]

Hello John,

I have tested your code and it worked.
Thanks for your help.

Chlaris