Question ODBC connection check function

[qqq]

Hi there
I am using the following function to check if the ODBC-connection is available each time the user runs the application. A few weeks it started to give wrong results: when i run the function right after starting the application it sometimes (not always) faces an odbc-connection error, telling me I'm offline, but I'm not. I tried using different users and passwords, same issues. Strange enought, it has no problems opening linked tables with the same combination of user & password.
Any ideas? Any other ways of checking for an ODBC-connection more gracefully?
qqq

===================================
Public Function FtnIsOnline() As Boolean

'to determine whether there's a connectiont to the SQL server or not
On Error GoTo ErrorHandler

Dim wrkODBC As Workspace
Dim conPubs As Connection

' Create ODBCDirect Workspace object.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)

Set conPubs = wrkODBC.OpenConnection("c", dbDriverNoPrompt, , "ODBC;DATABASE=MyDatabase;DSN=MyConnection;UID=MyUser;PWD=MyPassword")

conPubs.Close

FtnIsOnline = True

GoTo Exiter

ErrorHandler:
If Err.number = 3146 Or Err.number = 3151 Then
FtnIsOnline = False
GoTo Exiter
Else
' ... normal error routine
End If

Exiter:
Set wrkODBC = Nothing
Set conPubs = Nothing

End Function
===================================
MySQL backend, tables linked using MySQL ODBC 5.1 Driver, front end running Access 2002 on WinXP

 

[Banana]

Will need a bit more information to help out.

What kind of error do you get that cause false negative result?

How are you connecting to MySQL? Just plain TCP/IP across the LAN? No WAN?

Why do a 2nd connection when we should be testing the linked tables' connection? I suspect you may get more accurate results if you used OpenDatabase method, but can't say for sure until I know why you're having this error in first place.

 

[qqq]

found workaround

Hi Banana
thanks for your assistance, sorry for the delayed reply (I thought I had posted back long since).
Seems like I found a simple workaround in the mean time: as you propose I just use one of the existing table links to check for the connection. This previously didn't work, because it would trigger a ODBC-connection pop-up to prompt me for a MySQL login, which I couldn't suppress and handle gracefully. Now it seems that having upgraded to ODBC-connector 5.1 this nasty pop-up is effectively avoided, so that I can use a normal error routine to evaluate the result of the connection attempt.
Still no idea why the connection string didn't work sometimes, but did other times. Am not using Wifi, but LAN, the error I got was getting is #3151 "ODBC connection failed".
Anyway, thanks for your efforts!
qqq

 

[Banana]

Can you post your connection string? (you can omit the sensitive data)

 

[qqq]

I thought the code I posted earlier contained connection string already:

Set conPubs = wrkODBC.OpenConnection("c", dbDriverNoPrompt, , "ODBC;DATABASE=MyDatabase;DSN=MyConnection;UID=MyUser;PWD=MyPassword")

Anyway using a dlookup for the connection check works fine now.

qqq