Detect failed ODBC connection when vpn is not up?

Detect failed ODBC connection when vpn is not up?

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.

date: Sat, 5 Jul 2008 23:49:38 -0700 author: amos

Re: Detect failed ODBC connection when vpn is not up?

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"  wrote in message 
news:MPG.22da0ad91f71b1a89896eb@msnews.microsoft.com...
> 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.

date: Sun, 6 Jul 2008 11:05:15 +0400 author: Alex Dybenko

Re: Detect failed ODBC connection when vpn is not up?

In article , 
alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru says...
> 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.

date: Sun, 6 Jul 2008 00:23:05 -0700 author: amos

Re: Detect failed ODBC connection when vpn is not up?

In article , 
alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru says...
> 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

date: Sun, 6 Jul 2008 18:16:35 -0700 author: amos

Re: Detect failed ODBC connection when vpn is not up?

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

"amos"  wrote in message 
news:MPG.22db0e501871105d9896ed@msnews.microsoft.com...
> In article ,
> alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru says...
>> 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

date: Mon, 7 Jul 2008 11:23:16 +0400 author: Alex Dybenko