How do I close a record set that is not open?

[Guest]

Consider the following pseudo code :-


Public Sub MySubRoutine()

Dim rst As DAO.Recordset

On Error GoTo ErrorCode

'some initial code here

Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
Do Until rst.EOF
'process recordset data here
rst.MoveNext
Loop
rst.Close

'some tidy up code here

Exit Sub

ErrorCode:
rst.Close 'is this line necessary ???
MsgBox Err.Description

End Sub


If the code runs OK then no problem and if an error occurs within the
Do-Loop section the rst.Close in the error handler section will close the
recordset OK. However, if an error occurs in the *initial* or *tidy up* code
sections, the second rst.Close command will generate another error because
the record set is not open or has already been closed. If I leave out this
command the procedure will terminate with the record set still open although
I believe Access will close it anyway. If this is the case do I need to close
the record set at all, just let Access do it on exit.

The question is how would the experts code this situation?

 

[Alessandro Baraldi]

Peter Hibbs ha scritto:

Consider the following pseudo code :-


Public Sub MySubRoutine()

Dim rst As DAO.Recordset

On Error GoTo ErrorCode

'some initial code here

Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
Do Until rst.EOF
'process recordset data here
rst.MoveNext
Loop
rst.Close

'some tidy up code here

Exit Sub

ErrorCode:
rst.Close 'is this line necessary ???
MsgBox Err.Description

End Sub


If the code runs OK then no problem and if an error occurs within the
Do-Loop section the rst.Close in the error handler section will close the
recordset OK. However, if an error occurs in the *initial* or *tidy up* code
sections, the second rst.Close command will generate another error because
the record set is not open or has already been closed. If I leave out this
command the procedure will terminate with the record set still open although
I believe Access will close it anyway. If this is the case do I need to close
the record set at all, just let Access do it on exit.

The question is how would the experts code this situation?

To destroy all not necessary object is a good method to clean stack
memory...

To close and destroy the DAO.Recordset Object only if exist you can try
with this:

If Not rs Is Nothing Then rs.Close
Set rs=nothing

@Alex

 

[Granny Spitz via AccessMonster.com]

If I leave out this
command the procedure will terminate with the record set still open although
I believe Access will close it anyway. If this is the case do I need to close
the record set at all, just let Access do it on exit.

You *must* close the DAO recordset before the procedure ends because the
object might not be released from memory at procedure termination, causing a
memory leak. ADO doesn't have this problem so you might want to use ADO
recordsets instead.

The question is how would the experts code this situation?

Many of them use On Error Resume Next inside the error handler. If closing
the recordset when it's already closed causes an error, they never hear about
it.

 

[Guest]

Thanks guys, I will try those solutions.

 

[Granny Spitz via AccessMonster.com]

Thanks guys, I will try those solutions.

You're welcome, Peter. Good luck with your application.