ACC97 Closing Recordsets within Transactions

[amazo]

Dear all,

I would like to know if it is possible to close used recordsets in the
middle of transactions (and re-use the variable it is is necessary),
i.e.

--- begin code
Dim ws As DAO.Workspace
Dim db As DAO.Database
dim rst as DAO.Recordset

Set ws = DBEngine(0)
ws.BeginTrans
Set db = ws(0)

set rst = currentdb.openrecordset("table1",dbopendynaset)
... update table1
rst.close
set rst = nothing

set rst = currentdb.openrecordset("table2",dbopendynaset)
... update table2
rst.close
set rst = nothing

ws.CommitTrans
set db = nothing
set ws = nothing
--- end code

Thanks in advance.

Angel.

 

[Salad]

Dear all,

I would like to know if it is possible to close used recordsets in the
middle of transactions (and re-use the variable it is is necessary),
i.e.

--- begin code
Dim ws As DAO.Workspace
Dim db As DAO.Database
dim rst as DAO.Recordset

Set ws = DBEngine(0)
ws.BeginTrans
Set db = ws(0)

set rst = currentdb.openrecordset("table1",dbopendynaset)
... update table1
rst.close
set rst = nothing

set rst = currentdb.openrecordset("table2",dbopendynaset)
... update table2
rst.close
set rst = nothing

ws.CommitTrans
set db = nothing
set ws = nothing
--- end code

Thanks in advance.

Angel.

I do it all the time.

 

[Allen Browne]

Yes, that's fine.

Setting to Nothing before reusing the variable is good practice.

The only thing I do slightly differently is to move the line:
Set db = Nothing
up one. db depends on ws, so I dereference db and then commmit the
transaction.

 

[David Emme]

Setting to Nothing before reusing the variable is good practice.

I read this over and over.

It sounds to me like (at least it should be) ancient folklore from
Access 2 or so. Is Access (still) really that bad about cleaning up
local variables on the stack when a routine exits? This is not rocket
science, after all. How can I know that this is really worth the bother
in Access 97 or newer?

-Dave

 

[Brendan Reynolds]

You're probably right, David, but note that Allen was not talking about
setting variables to nothing before exiting the procedure. He was talking
about setting variables to nothing before re-using them.

In production code, I still set my object variables to Nothing when I'm
finished with them, even if that is at the end of the procedure, because it
is possible that additional code may be added after that point in the
future. But I've been gradually weaning myself from the habit of setting all
variables to Nothing in my ExitProcedure routines, without any harmful
effects so far ...

Public Sub SomeSub

Dim db As DAO.Database
...
'I still do this
Set db = Nothing

'Because I or someone else may add more code here
...

ExitProcedure:
'But I don't bother doing this any more ...
On Error Resume Next
Set db = Nothing
Exit Sub

ErrorHandler:
...

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Michael \(michka\) Kaplan [MS]]

You never HAVE TO set to nothing before reusing, but it can find bugs that
would happen if you started using a variable before doing a new
initialization of it.

At the end of a proc is never needed -- only the close is required (if you
opened it).


--
MichKa [MS]
NLS Collation/Locale/Keyboard Technical Lead
Globalization Infrastructure and Font Technologies
Windows International Division

This posting is provided "AS IS" with
no warranties, and confers no rights.

You're probably right, David, but note that Allen was not talking about
setting variables to nothing before exiting the procedure. He was talking
about setting variables to nothing before re-using them.

In production code, I still set my object variables to Nothing when I'm
finished with them, even if that is at the end of the procedure, because it
is possible that additional code may be added after that point in the
future. But I've been gradually weaning myself from the habit of setting all
variables to Nothing in my ExitProcedure routines, without any harmful
effects so far ...

Public Sub SomeSub

Dim db As DAO.Database
...
'I still do this
Set db = Nothing

'Because I or someone else may add more code here
...

ExitProcedure:
'But I don't bother doing this any more ...
On Error Resume Next
Set db = Nothing
Exit Sub

ErrorHandler:
...

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.