By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,220 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,220 IT Pros & Developers. It's quick & easy.

How to quick-check SQL connection

P: n/a
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.Connection

' No problem with the following connection block - no timeout
' ---------------------------------------------------------
Set conn = New ADODB.Connection
With conn
.ConnectionString = CurrentProject.BaseConnectionString
.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_MySQLLinkedTable"
.ActiveConnection = conn
.CommandTimeout = 10 ' shorten delay to 10 seconds
Set objCmd = .Execute
End With

Nov 18 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

You haven't set the ConnectionTimeout property of the connection object

ConnectionTimeout: 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*********************@g43g2000cwa.googlegro ups.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.Connection

' No problem with the following connection block - no timeout
' ---------------------------------------------------------
Set conn = New ADODB.Connection
With conn
.ConnectionString = CurrentProject.BaseConnectionString
.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_MySQLLinkedTable"
.ActiveConnection = conn
.CommandTimeout = 10 ' shorten delay to 10 seconds
Set objCmd = .Execute
End With

Nov 18 '05 #2

P: n/a
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.

Nov 18 '05 #3

P: n/a
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*********************@g43g2000cwa.googlegro ups.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.Connection

' No problem with the following connection block - no timeout
' ---------------------------------------------------------
Set conn = New ADODB.Connection
With conn
.ConnectionString = CurrentProject.BaseConnectionString
.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_MySQLLinkedTable"
.ActiveConnection = conn
.CommandTimeout = 10 ' shorten delay to 10 seconds
Set objCmd = .Execute
End With

Nov 18 '05 #4

P: n/a
Well I'm guessing your doing something wrong then. The ConnectionTimeout
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.googlegr oups.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.

Nov 19 '05 #5

P: n/a
Try this:

If Application.CurrentProject.IsConnected 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.Connection
Set conn = New ADODB.Connection
With conn
.ConnectionString = CurrentProject.BaseConnectionString
.CursorLocation = adUseClient
.CommandTimeout = 10 ' shorten delay to 10 seconds
.Open
End With


Nov 19 '05 #6

P: n/a
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........ugh.

I am now setting the .ConnectionString 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 .ConnectionTimeout .
Thanks for looking at it with me

Nov 21 '05 #7

P: n/a
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*********************@z14g2000cwz.googlegro ups.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........ugh.

I am now setting the .ConnectionString 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 .ConnectionTimeout .
Thanks for looking at it with me

Nov 21 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.