How to quick-check SQL connection

[ZRexRider]

Hi,

I'm developing an application that runs on laptops that periodically
connect to network. I am collecting data in local tables within the
MDB. I want to recognize the occasional network connection so I can
upload the collected data to SQL server and retrieve updated look-up
information.

This application has 2 linked SQL server tables that are not used until
I find myself connected to the network. Since I have these links, I
figured I could use them with timeout to "guess" my connection status.

So far this works but it takes the default 30 seconds to timeout. I'd
hate to force the user to wait 30 seconds every time they start the
application off-line.

I through together the following code to see if I could alter the
timeout (make it a bit sooner) but it has no effect. Has anybody else
ever done something like this? Is there a better way to detect the
network connection?

' testing with SQL Server shut down
' ---------------------------------------------------------
Dim objRS As New ADODB.Recordset
Dim objCmd As ADODB.Command
Dim conn As ADODB.Connectio n

' No problem with the following connection block - no timeout
' ---------------------------------------------------------
Set conn = New ADODB.Connectio n
With conn
.ConnectionStri ng = CurrentProject. BaseConnectionS tring
.CursorLocation = adUseClient
.CommandTimeout = 10 ' shorten delay to 10 seconds
.Open
End With

' despite the fact that I've set the commandtimeout of the
' connecton to 10 seconds AND I've set the commandtimeout
' of the following ADODB.Command to 10 seconds - the following
' query takes 43 seconds to timeout
' ---------------------------------------------------------
Set objCmd = New ADODB.Command
With objCmd
.CommandType = adCmdText
.CommandText = "SELECT * FROM dbo_MySQLLinked Table"
.ActiveConnecti on = conn
.CommandTimeout = 10 ' shorten delay to 10 seconds
Set objCmd = .Execute
End With

[Terry Kreft]

You haven't set the ConnectionTimeo ut property of the connection object

ConnectionTimeo ut: Indicates how long to wait while establishing a
connection before terminating the attempt and generating an error.

As opposed to

CommandTimeout: Indicates how long to wait while executing a command before
terminating the attempt and generating an error.

--
Terry Kreft

"ZRexRider" <je****@ptd.net > wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.com...

Hi,

I'm developing an application that runs on laptops that periodically
connect to network. I am collecting data in local tables within the
MDB. I want to recognize the occasional network connection so I can
upload the collected data to SQL server and retrieve updated look-up
information.

This application has 2 linked SQL server tables that are not used until
I find myself connected to the network. Since I have these links, I
figured I could use them with timeout to "guess" my connection status.

So far this works but it takes the default 30 seconds to timeout. I'd
hate to force the user to wait 30 seconds every time they start the
application off-line.

I through together the following code to see if I could alter the
timeout (make it a bit sooner) but it has no effect. Has anybody else
ever done something like this? Is there a better way to detect the
network connection?

' testing with SQL Server shut down
' ---------------------------------------------------------
Dim objRS As New ADODB.Recordset
Dim objCmd As ADODB.Command
Dim conn As ADODB.Connectio n

' No problem with the following connection block - no timeout
' ---------------------------------------------------------
Set conn = New ADODB.Connectio n
With conn
.ConnectionStri ng = CurrentProject. BaseConnectionS tring
.CursorLocation = adUseClient
.CommandTimeout = 10 ' shorten delay to 10 seconds
.Open
End With

' despite the fact that I've set the commandtimeout of the
' connecton to 10 seconds AND I've set the commandtimeout
' of the following ADODB.Command to 10 seconds - the following
' query takes 43 seconds to timeout
' ---------------------------------------------------------
Set objCmd = New ADODB.Command
With objCmd
.CommandType = adCmdText
.CommandText = "SELECT * FROM dbo_MySQLLinked Table"
.ActiveConnecti on = conn
.CommandTimeout = 10 ' shorten delay to 10 seconds
Set objCmd = .Execute
End With

[ZRexRider]

Thanks Terry....

Unfortunately it had no effect. My query attempt still takes 43
seconds to time out (odd number huh?) no matter what timeout promperty
I set.

[paii, Ron]

I don't know the API calls, but have you thought about PINGing the server. A
single ping should respond much quicker then 30 sec.

"ZRexRider" <je****@ptd.net > wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.com...

Hi,

I'm developing an application that runs on laptops that periodically
connect to network. I am collecting data in local tables within the
MDB. I want to recognize the occasional network connection so I can
upload the collected data to SQL server and retrieve updated look-up
information.

This application has 2 linked SQL server tables that are not used until
I find myself connected to the network. Since I have these links, I
figured I could use them with timeout to "guess" my connection status.

So far this works but it takes the default 30 seconds to timeout. I'd
hate to force the user to wait 30 seconds every time they start the
application off-line.

I through together the following code to see if I could alter the
timeout (make it a bit sooner) but it has no effect. Has anybody else
ever done something like this? Is there a better way to detect the
network connection?

' testing with SQL Server shut down
' ---------------------------------------------------------
Dim objRS As New ADODB.Recordset
Dim objCmd As ADODB.Command
Dim conn As ADODB.Connectio n

' No problem with the following connection block - no timeout
' ---------------------------------------------------------
Set conn = New ADODB.Connectio n
With conn
.ConnectionStri ng = CurrentProject. BaseConnectionS tring
.CursorLocation = adUseClient
.CommandTimeout = 10 ' shorten delay to 10 seconds
.Open
End With

' despite the fact that I've set the commandtimeout of the
' connecton to 10 seconds AND I've set the commandtimeout
' of the following ADODB.Command to 10 seconds - the following
' query takes 43 seconds to timeout
' ---------------------------------------------------------
Set objCmd = New ADODB.Command
With objCmd
.CommandType = adCmdText
.CommandText = "SELECT * FROM dbo_MySQLLinked Table"
.ActiveConnecti on = conn
.CommandTimeout = 10 ' shorten delay to 10 seconds
Set objCmd = .Execute
End With

[Terry Kreft]

Well I'm guessing your doing something wrong then. The ConnectionTimeo ut
property is the one to be using/

What value have you set it at?

--
Terry Kreft

"ZRexRider" <je****@ptd.net > wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

Thanks Terry....

Unfortunately it had no effect. My query attempt still takes 43
seconds to time out (odd number huh?) no matter what timeout promperty
I set.

[PhilEnd]

Try this:

If Application.Cur rentProject.IsC onnected Then...

I'm not sure it works with .mdb but it works fine with .adp and it's
wort to give it a try.

ZRexRider wrote:

I'm developing an application that runs on laptops that periodically
connect to network. I am collecting data in local tables within the
MDB. I want to recognize the occasional network connection so I can
upload the collected data to SQL server and retrieve updated look-up
information.
...
Dim conn As ADODB.Connectio n
Set conn = New ADODB.Connectio n
With conn
.ConnectionStri ng = CurrentProject. BaseConnectionS tring
.CursorLocation = adUseClient
.CommandTimeout = 10 ' shorten delay to 10 seconds
.Open
End With

[ZRexRider]

And it would be a good guess Terry.

Turns out that I was using the connection of the MDB itself instead of
the connection of the linked table that I intended to query. I would
set my new connection object connection string to that of the MDB, set
the timeout of this connection and then query the linked SQL server
table........ug h.

I am now setting the .ConnectionStri ng to value of the "Connect" field
in the MSysObjects table and THEN trying a query. Now it only takes
whatever amount of time I set in the .ConnectionTime out .
Thanks for looking at it with me

[Terry Kreft]

LOL, easily done, you get your head so stuck into the code and the
technicalities sometimes you forget the data.
--
Terry Kreft

"ZRexRider" <je****@ptd.net > wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...

And it would be a good guess Terry.

Turns out that I was using the connection of the MDB itself instead of
the connection of the linked table that I intended to query. I would
set my new connection object connection string to that of the MDB, set
the timeout of this connection and then query the linked SQL server
table........ug h.

I am now setting the .ConnectionStri ng to value of the "Connect" field
in the MSysObjects table and THEN trying a query. Now it only takes
whatever amount of time I set in the .ConnectionTime out .
Thanks for looking at it with me