Test for Valid ODBC Connection

[Scott A. Jones]

Hello,

I am in the process of 'figuring out' how to verify if I have a valid
connection to an ODBC data source (SQL Server 2000). I've written code to
run a pass-through query against a table that is in the database. If the
query fails, I want the function to return a 'FALSE'. The code works fine
if the connection exists (returns 'TRUE'). However, if the connection is
invalid, I am forced to deal with an ODBC login dialog box that I can't seem
to suppress.

Perhaps I'm tackling this wrong. Has anybody tried to accomplish this same
task? If so, how did you do it?

Here's my code, for review:

************************************************** *********************
Public Function CheckSQLLinks() As Boolean
' Check links to a table (strSampleSQLTableName constant) defined
' in the module 'modPublic' module. Returns True if links are OK.

Dim db1 As Database, rs1 As Recordset, strSQL1 As String

On Error GoTo ErrOnLink

Set db1 = CurrentDb
strSQL1 = "SELECT " & strSampleSQLTableName & ".* " & "FROM " &
strSampleSQLTableName & ";"

Set rs1 = db1.OpenRecordset(strSQL1, dbOpenDynaset)

'Connection is OK, continue
CheckSQLLinks = True

ExitOnLink:
db1.Close
Exit Function

ErrOnLink:
CheckSQLLinks = False
GoTo ExitOnLink

End Function
************************************************** *************************

Thanks,
Scott

[Alex Dybenko]

Hi Scott,
just tried your code - works fine at me, no ODBC login dialog. I tried on
access XP. What Access version you using?

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com
"Scott A. Jones" <sa*****@gmx.net> wrote in message
news:ci*********@enews1.newsguy.com...

Hello,

I am in the process of 'figuring out' how to verify if I have a valid
connection to an ODBC data source (SQL Server 2000). I've written code to
run a pass-through query against a table that is in the database. If the
query fails, I want the function to return a 'FALSE'. The code works fine
if the connection exists (returns 'TRUE'). However, if the connection is
invalid, I am forced to deal with an ODBC login dialog box that I can't seem to suppress.

Perhaps I'm tackling this wrong. Has anybody tried to accomplish this same task? If so, how did you do it?

Here's my code, for review:

************************************************** *********************
Public Function CheckSQLLinks() As Boolean
' Check links to a table (strSampleSQLTableName constant) defined
' in the module 'modPublic' module. Returns True if links are OK.

Dim db1 As Database, rs1 As Recordset, strSQL1 As String

On Error GoTo ErrOnLink

Set db1 = CurrentDb
strSQL1 = "SELECT " & strSampleSQLTableName & ".* " & "FROM " &
strSampleSQLTableName & ";"

Set rs1 = db1.OpenRecordset(strSQL1, dbOpenDynaset)

'Connection is OK, continue
CheckSQLLinks = True

ExitOnLink:
db1.Close
Exit Function

ErrOnLink:
CheckSQLLinks = False
GoTo ExitOnLink

End Function
************************************************** *************************
Thanks,
Scott

[Scott A. Jones]

Hi Alex,

Thanks for the reply.

I'm using Access 2003. I just tried the project in Access XP and, as you
said, it works fine. This must be a bug/feature in Access 2003.

It was suggested in another thread that I try usind ADO to test my
connection rather than DAO. Apparantly I won't encounter the problem.

Thanks,
Scott