Verify ado connections

[deepsheep]

We have a number of small programs that connect to a Microsoft SQL server via an ADO connection over a network. The connection is opened when the program starts and close when it ends. Our programs need to run all the time.

We have been encountering a network bug that causes all our machines on the network to be unresponsive for a couple of minutes. All our little programs then come up with "connection error" or "general network error". We can replicate the errors by unplugging our SQL machine from the network for approx 2 min. We think we have found most of the network problems, but want to enchance or programs so if this returns it won't cause a problem.

In the programs I want to check to see if I acctually have a live connection to the SQL server. What I have been doing is this:

if adocon.state=0 then 'closed
do
doevents
'try to connect
until adocon.state>0'not closed
do
doevents
until adocon.state=1 'connected (not some other state
end if

This doesn't seem to work properly. I think it is because the connection thinks it's connceted, even though there is no physical connection.

What's a better way of doing this?

 

[CCLINT]

Declare the Connect at module level, in the declaration section, using WithEvents.
Now you have a few connection events available to you, one called ConnectComplete.
Use this to determine to determine when the connection is made, before proceeding further with any other ADO code.
Also use the Connection Timeout property.
See if this helps.

 

[deepsheep]

I had been looking at the events, but I'm still not sure how that can help me when I can connect successfully, but later ( an hour, six hours or days later) something happens to the physical connection for a few minutes. If nothing tries to access the database during the time the connection is messed up, the program's fine.

Could you elaborate about the events?

I will look into the timeout property an see what it can do for me.

Thanks.