Using one recordset name multiple times

  • Thread starter Thread starter PJFry
  • Start date Start date
P

PJFry

I have been developing a database that uses recordsets at various point. I
declare the recordset variables that I use like so:

Public cn As ADODB.Connection
Public rst As ADODB.Recordset
Public strSQL As String

Then, in a given form, I will use the rst like so:

Private Sub Form_Load()

Set rst = New ADODB.Recordset
Set cn = CurrentProject.Connection

strSQL = "SELECT * FROM tSomething"

rst.open strSQL, cn, adOpenStatic, adLockOptimistic

If rst!lngULevel Then
Do This
Else
Do That
End if

End Sub

Then I may use the exact same set up with a different if/case statement in
the same form. To date, this has not caused any problems.

Should I be closing the recordset at the end of each sub, or do the close be
default when that event is done?

I am still getting my recordset feet wet. Any thoughts?

PJ
 
I would prefer to open and close the recordset within it's own sub. Doing
that you can always close the recordset in the error handling of the sub (you
do have that don't you?). It is possible to create a generic error handler
where you can trap the various errors and close the recordset there.

my habit is to close the recordset at the end and set the recordset to
nothing like:

rst.close
set rst=nothing

hth
 
Thanks for the information. I will start to add the close and nothing
attributes.

It had always seemed like there was something incomplete in what I was
doing.
 
Since the recordset is declared as a public variable (presumably at the
module level), technically you should be closing it each time you're done
with it (and setting it to Nothing).
 
Taking Maurice's comments one step further, I usually do something like the
following. If an error occurs in your code, it will go to the error handler.
If not, the code will eventually move into the SubroutineExit area, and will
clean up the recordset (if it exists). In the error handler, I might post a
message, write data to a log, or handle a specific error differently, but for
those errors I don't handle uniquely, I'll generally resume processing at the
SubroutineExit, to make sure recordsets are closed and set to nothing.

On Error Goto SubroutineErrHandler
.... 'some event code here

SubroutineExit:
if not rst is nothing then
rst.close
set rst = nothing
endif
Exit Sub

SubroutineErrHandler:
'Do something to handle errors.
msgbox err.number & vbcrlf & err.description
Resume SubroutineExit

End Sub


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top