Detect failed ODBC connection when vpn is not up?

[amos]

Some users might not have the vpn connected when they start my app...the
odbc timeouts for the passthrough queries on the main form take a long
time (20-30 secs each) to return a failed odbc connection error. Does
anyone know of a quick way to test for an ok odbc connection? So far
I've not seen any difference when I change the odbc timeout setting in
either the db or the query.

 

[Alex Dybenko]

Hi,
you can use ADO to test the connection, where you can specify Connection
object timeout. Just create Connection object, set connection property to
your sql server, set timeout to 10 sec, try to open it and catch the error,
if any

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[amos]

Hi,
you can use ADO to test the connection, where you can specify Connection
object timeout. Just create Connection object, set connection property to
your sql server, set timeout to 10 sec, try to open it and catch the error,
if any

Thanks Alex - no way to do this without invoking ADO? Not my first
choice but better than nothing.

 

[amos]

Hi,
you can use ADO to test the connection, where you can specify Connection
object timeout. Just create Connection object, set connection property to
your sql server, set timeout to 10 sec, try to open it and catch the error,
if any

I'm trying this now. This db has no linked tables, all data is pulled
from the server using stored procedures. Could you give me a more
verbose example of how to do this, or a couple lines code?

I've not used ADODB at all, so nothing is obvious. I trying something
like this? Not sure what sql I can execute since there are no linked
tables. I suppose one would trap for the timeout error.

Dim conTemp As ADODB.Connection
Dim strSQL As String
'Open a new connection to the database
Set conTemp = New ADODB.Connection
With conTemp
.ConnectionString = CurrentProject.BaseConnectionString
.CursorLocation = adUseClient
.CommandTimeout = 10 '10 seconds to fail if vpn not up
.Open
End With
'Use conTemp to execute sql or with an ADO command object:
strSQL = "Some SQL"
conTemp.Execute strSQL, , adCmdText

 

[Alex Dybenko]

Hi,
yes, you are on the right track. For connection string you have to use like
here:
http://www.connectionstrings.com/?carrier=sqlserver

and for strSQL you can use
Select top 1 from Table1
where Table1 - is any table on SQL server

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com