mssql2k sqloledb.1 (re)connection failure

Hey all, 2 questions.

Is there a way to 'refresh' the value of adodb.connection.state? and When my ado class reconnects after a dropped connection i still get connection failure errors.

I'm trying to make the connection b/n the my work's app and mssql server more 'robust'.

Our ado class tests the connection in the 'execute' method ie if connect() execute db task

.connect() is

IF this.loConnection.State = adStateOpen
*-- The connection is already open.
ELSE
create connection
ENDIF

return (this.loConnection.State = adStateOpen)

Now, this works fine when starting up the app, and everything runs fine, but *in theory* it should be able re-establish the connection at any time. So i kill the connection @. on the sql server while the app is running and try to perform a task that requires a db connection.

problem is that 'this.loConnection.State' is still equal to 1 even though the connection is no longer there.

It gets changed to 0 when the .execute fails but the failure is less than graceful... and although it reconnects (sp_who shows a new connection), it seems like the ado object is trying to connect using the old connection rather than the new one. command.activeconnection is set to the new connection in the .connect method so i dunno what's up.

Any ideas?
If you look for DBPROP_CONNECTIONSTATUS you will see that the status can be
uninitialized, initialized, or communicationfailure.
You cannot get communicationfailure unless you execute something and it fails.|||I suspected that wouldn't be able to do anything with .State once the connection has been made.

Any tips re: the other problem? Is there anything else I need to recreate/reinitialise besides command.activeconnection when i open a new connection?
|||On a second thought the provider can actually check the connection when the property is requested by GetProperties. I think that SQLOLEDB might be doing this. I will have to check the code to see if this is the case, and also how ADO is refreshing the properties, unless of course somebody else does not come up with the answer sooner.|||Could you try checking "Connection Status" property of the ADO Connection?
I beleive that this is how DBPROP_CONNECTIONSTATUS is being exposed from a corresponding provider.