Way to detect when ADODB.Connection has lost its connection?

[mdlueck]

Is anyone aware of an attribute inside an ADODB.Connection object which may be checked to see if the object has lost its connection to the SQL BE DB and thus there is no point in trying to use the object for further SQL?

 

[speakers_86]

I don't know the answer, but couldn't you just try to read a table from the connection and catch the error?

 

[mdlueck]

I was thinking to peek inside the object when a DB class requests a pointer to the global shared ADODB.Connection object from the ObjBEDBConnection object which contains it. If that detects the object is dead, then present a customized error message and exit the application.

As-is, the application racks up failed SQL calls to the BE DB.

 

[Galaxiom]

Test its State property.

These are the enumerations. (Some are for Command.State.)

adStateClosed = 0
adStateOpen = 1
adStateConnecting = 2
adStateExecuting = 4
adStateFetching = 8

 

[Galaxiom]

Before I run a command I do a check first for it not being Nothing (because any reference to it will fail) then for the State. If it is Nothing I instantiate it. If it is Closed I open it.

This way I don't have to ever worry about it again even on the first command that uses the connection.

This can be held as a function with the object as an argument so it is just one line when preparing to run the command. It calls another function that handles the connection if necessary.

 

[mdlueck]

Test its State property.

adStateOpen = 1

Excellent, Galaxiom!!!

I was able to greatly enhance my class which maintains the Connection object. Now instead of blindly hoping that the connection is still valid, the Get method of the class fires a Check method, if that fails it then calls Connect to destroy/rebuild the object, Connect also calls Check, if even that fails, then Get now returns Nothing.

** Oh, in my testing, the state of a normal / connected object appears to be adStateOpen. **

In a data replication portion of this application, I can keep a failed counter, and if that climbs high enough I will code it to stop trying to replicate. hhhmmm... should add a fail counter to the class... next!

 

[mdlueck]

Gggggrrrr.....

I opened my application, then issued

ipconfig /release

at the command prompt. I attempted application usage, and the ADO Connection object never realized that it had lost the connection to the BE DB.

An ADO.Parameters object ends up being the first indication that there is no longer a connection. This is consistent with production connection failures I have observed in the past.

So evidently the following code example...

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.ADODBConnectionObj()
    .CommandText = "clsObjPartsTbl_LocatePartByID"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    [B][COLOR=Red].Parameters("@partid").Value = Me.id[/COLOR][/B]

    Set adoRS = .Execute()
  End With

VBA can make it through the call to .ActiveConnection which goes through my new adoConn checker code, and a call to .Parameters.Refresh... only when it gets to the red LOC does it realize that it has lost the connection to the BE DB.

Back to the drawing board...

 

[mdlueck]

This is an example of the type of error that indicates a connection failure...

Date: 20121026 Time: 10:52:06 UserID: c_mlueck
AppErrorMsg: Class: clsObjPartsTbl, Function: LocatePartByID()
[B][COLOR=Red]Error Source: ADODB.Parameters[/COLOR][/B]
Error Number: 3265
Error Description: Item cannot be found in the collection corresponding to the requested name or ordinal.
MessageText: Error not found.

I added this code to my global error handler routine...

  'Check the source of the error to see if it is a connection type error
  If strErrSource = "ADODB.Parameters" Then
    'Increment the ADO.Connection error counter
    Call ObjBEDBConnection.IncrementFailCount
  End If

When the FailCount reaches a certain threshold, Access is instructed to display a custom error message and then terminate immediately. For now I have the threshold set to 4. Example of the error message prior to application exit...

Date: 20121026 Time: 10:52:07 UserID: c_mlueck
AppErrorMsg: Class:  clsObjBEDBConnection, Subroutine: IncrementFailCount(), [B][COLOR=Red]Error: Reached the Max Failed Connection Level of 4. Exiting Fandango FE NOW![/COLOR][/B]
Error Source: [null]
Error Number: 0
Error Description: [null]
MessageText: The operation completed successfully.