Best Method to Handle ADO Errors 1

[mikemcginty]

What is the best method to handle ADO recordset errors to ensure propper cleanup while leaving the active connection open?

The following code gives an error 3219 "Operation is not allowed in this context" if I put the Done before closing the recordset. Does this mean the recordset is already closed by the error?

If I set the recordset to Nothing, does this automatically close the recordset first (ie is .close necessary)?

Thanks

Mike


Dim rsRecordset as New ADODB.Recordset

On error Goto TrapError

Set rsRecordset = activeconnection

.source = "select...."

.open

code

rsRecordset.close
Done:
set rsRecordset = Nothing

Exit Sub

TrapError:

on error goto Done

resume next

End Sub


When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[lone84]

Hi,

I am not too sure about what you are asking.
However, sometimes you may face problems when using ado.
You may be adding a record, but before your adding finishes, you try to search for a record. This action may bring about an error. In order to solve this, using the cancelupdate function.

Hope this helps :-D

 

[CCLINT]

>is .close necessary
Yes, always do this. While the recordset object variable may be set to Nothing, the actual recordset may not immediately close completely, and then the cache may not get flushed.
Explicitly closing the recordset will destroy the internal cache - which is what we want to do.
Not explicitly closing the recordset object may make a difference between a successful application....and one which sooner or later fails.
The same holds true for the connection object. Close this and then Set it to Nothing, once the application ends, or when it is no longer needed.

Done:
if rsRecordset.State = adStateOpen Then rsRecordset.close
set rsRecordset = Nothing
Exit Sub

TrapError:
MsgBox Err.Number & " " & Err.Description

If rsRecordset.State = adStateOpen Then
Dim ErrorsList as ADODB.Errors
Dim connError As ADODB.Error
Set ErrorsList = myRS.ActiveConnection.Errors
For Each connError In ErrorsList
If Err.Number <> connError.Number Then
MsgBox connError.Number & &quot; &quot; & connError.Description
End If
Next connError
End If
End Sub

 

[mikemcginty]

Thanks CCLINT

I'm still confused about error handling as per your code example

I assume there is a Goto Done just before the End Sub in the error handler

I tried the following

Done:
on error resume next
if rsRecordset.State = adStateOpen Then rsRecordset.close

but I always get an error 3219 &quot;Operation is not allowed in this context&quot; relating to the .close instruction when I simulate an error

The same error occurs for my other subroutines accessing the database

I checked out Microsoft Knowledge Base Article 168336 and the AdoErrorLite handler doesn't seem to attempt to clean up recordsets before setting the varaible to nothing (although I always trust you wisdom)

I thought the Err handler is for recordset errors and the ado errors collection is for the active connection errors (ie only used when opening and closing the ado connection not the recordset connection) because I both give the same error description output when I trapped a recordset error

Regards

Mike

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[CCLINT]

They are not always the same errors, and there could be more that one error, in which case the Errors collection will catch.

Take your code from the original post and just replace everything from

rsRecordset.close

on down, with the code I posted.

 

[mikemcginty]

Do I need to insert Goto Done before End Sub?

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[CCLINT]

Aaarrggg! Yes! Missed when it from my copy/paste-ing!

Good catch.
But use a Resume instead of Goto:

TrapError:
MsgBox Err.Number & &quot; &quot; & Err.Description

If rsRecordset.State = adStateOpen Then
Dim ErrorsList as ADODB.Errors
Dim connError As ADODB.Error
Set ErrorsList = myRS.ActiveConnection.Errors
For Each connError In ErrorsList
If Err.Number <> connError.Number Then
MsgBox connError.Number & &quot; &quot; & connError.Description
End If
Next connError
End If
Resume Done
End Sub

 

[mikemcginty]

Ok I tried and it worked ok

I was previously calling a subroutine with similar code to look at the errors collection

It's late here but I'll try again tomorrow to find out the difference between what you gave me and what I had previously

Thanks for your help once again. Appreciated

Mike

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[mikemcginty]

In the cold light of day I tested my code with CCLINT's suggested changes and it appears to be working fine

Just shows how easy it is to stop the errors by doing things the wrong way. I am much more comfortable now knowing its works and it was done the right way.

Thanks CCLINT

MIke

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!