Detect failed ODBC connection when vpn is not up?
in [Forms]
|
Prev: ListBox Question
Next: Form Goes Into Break mode A97
From: amos on 6 Jul 2008 02:49 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.
From: Alex Dybenko on 6 Jul 2008 03:05 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" <amos(a)amos2.com> wrote in message news:MPG.22da0ad91f71b1a89896eb(a)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.
From: amos on 6 Jul 2008 03:23 In article <eFNSmaz3IHA.4856(a)TK2MSFTNGP02.phx.gbl>, alexdyb(a)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.
From: amos on 6 Jul 2008 21:16 In article <eFNSmaz3IHA.4856(a)TK2MSFTNGP02.phx.gbl>, alexdyb(a)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
From: Alex Dybenko on 7 Jul 2008 03:23 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" <amos(a)amos2.com> wrote in message news:MPG.22db0e501871105d9896ed(a)msnews.microsoft.com... > In article <eFNSmaz3IHA.4856(a)TK2MSFTNGP02.phx.gbl>, > alexdyb(a)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
|
Pages: 1 Prev: ListBox Question Next: Form Goes Into Break mode A97 |