Error trapping for ADODB.Connection

[nod]

I have the following code derived from various examples:

The original error trapping seemed to be faulty and sometimes ended up
in a continuous loop so I removed it while I developed the
application.

Now I should be putting error trapping back in again.It strikes me
that control needs to be transferred to different points lower down
the code depending on where an error occurs. Otherwise further errors
are generated.

e.g.if an error occurs after the recordset is opened
control should pass to a point where the recordset will be closed,
but not if the recordset has not been opened.
Similarly I presume I should make provision to call
conConnection.Close

However my understanding of what happens during the opening and
closing of a connection is pretty much nil.
I would be grateful if someone could point out the points where I
should be inserting code to trap errors and then recover from them.


Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim myRecordSet As New ADODB.Recordset

conConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path &
"\my.mdb;Mode=Read|Write"
conConnection.CursorLocation = adUseClient
conConnection.Open

With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM etc etc ;"
.CommandType = adCmdText
End With

With myRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With

If myRecordSet.EOF = False Then
myRecordSet.MoveFirst
Do
' whatever needs doing
myRecordSet.MoveNext
Loop Until myRecordSet.EOF = True
myRecordSet.Close
Else
MsgBox "No records were returned"
End If

conConnection.Close
Set conConnection = Nothing
Set cmdCommand = Nothing
Set myRecordSet = Nothing

 

[Douglas J. Steele]

I always use the approach

On Error GoTo ErrorHandler




ExitBit:
On Error Resume Next
conConnection.Close
Set conConnection = Nothing
Set cmdCommand = Nothing
Set myRecordSet = Nothing
Exit Sub

ErrorHandler:
' whatever
Resume ExitBit

End Sub

Since you've use the New keyword in your declaration, each of the objects
will at least be instantiated, so there's no point in checking if they're
instantiated.

I'd actually recommend using

Dim conConnection As ADODB.Connection
Dim cmdCommand As ADODB.Command
Dim myRecordSet As ADODB.Recordset

Set conConnection = New ADODB.Connection
Set cmdCommand = New ADODB.Command
Set myRecordSet = New ADODB.Recordset

instead of what you've got.