Scope of Db and Rst objects: General questions

[Paul Johnson]

I am looking for answers to four related questions here.

In almost every code example I come across in books or in online tutorials,
a Recordset object is opened like this:

Private Sub Example()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SomeTable", dbOpenDynaset)
[do actions here]
Set rst = Nothing
Set db = Nothing
End Sub

I prefer getting the same results with the following compact code:

Private Sub Example()
With CurrentDb.OpenRecordset("SomeTable", dbOpenDynaset)
[do actions here]
.Close
End With
End Sub

Question 1: In Example 1, are the objects set as dimensioned variables only
to make the code more understandable to novices, or in a multi-developer
workplace, or is there a compelling reason that's beyond my limited
computer-science background?

Question 2: Is there any harm in creating the recordset object directly
from the CurrentDb object, as in Example 2?

Question 3: Is it really necessary to set these objects to Nothing, as in
Example 1, or close the recordset in Example 2? I thought that the scope of
procedure variables and objects (at least in these Private Subs) is limited
to the current procedure, and that they vanish when the procedure is exited.
I keep hearing that this cleanup is good practice, but what consequence are
we avoiding?

Question 4: Since I'm compiling and creating an MDE file for distribution,
I'm not concerned about the readability of the code in the end. Also, I'm
the only developer likely to work on this application, and I am accustomed
to my own habits. If I can shrink my code throughout like the above example
shows, does it make any difference once Access compiles it, or does the
compiler turn both types of code into the same set of instructions anyway
(like the way it ignores white space and comments)?

I have not been diligent in closing my recordsets in the past, and I have
not noticed any problems coming from it, but I have been curious if that is
what contributes to the increasing size of the mdb file. I'm guessing there
must be pieces of code or table data getting tagged to the end of the file
and not cleared from the buffer until the Compact/Repair command is
executed. I recently stepped through my code and closed all open objects
and set dimensioned object variables to Nothing before exiting subs, and the
file growth continues as before. It's not a problem, as the Compact command
keeps it in check (I have the "Compact on Exit" option set), but it made me
wonder about the above items.

Thanks in advance for any input.

Paul Johnson

 

[Allen Browne]

Good questions, Paul.

The With block will work, causing Access to hold open the object variable
created by CurrentDb. This object will probably be dereferenced correctly,
but we are just a bit unsure about that, because there was a bug in A97
where it failed to release the variable if it was not explicitly set to
Nothing, and you cannot set it to nothing if you did not declare an object.
This was *such* a pain to debug, and actually prevented Access from closing
(short of killing it with Ctrl+Alt+Del), so many of us like to declare the
object and know for sure that we are able to dereference it.

This bug seems to have been fixed. However, there are other bugs in the more
recent versions that can cause concurrency errors, e.g. "Another user has
changed...". Explicitly closing your recordsets and dereferencing all
objects is one obvious step towards preventing the concurrency problems.

You should see only very limited bloating of a split mde, unless you are
creating and destroying temporary tables/queries in the front end, or adding
and deleting records in the front end's tables.

In short, your briefer code would be okay in a perfect world. Unfortunately,
that's not where we live.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am looking for answers to four related questions here.

In almost every code example I come across in books or in online tutorials,
a Recordset object is opened like this:

Private Sub Example()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SomeTable", dbOpenDynaset)
[do actions here]
Set rst = Nothing
Set db = Nothing
End Sub

I prefer getting the same results with the following compact code:

Private Sub Example()
With CurrentDb.OpenRecordset("SomeTable", dbOpenDynaset)
[do actions here]
.Close
End With
End Sub

Question 1: In Example 1, are the objects set as dimensioned variables only
to make the code more understandable to novices, or in a multi-developer
workplace, or is there a compelling reason that's beyond my limited
computer-science background?

Question 2: Is there any harm in creating the recordset object directly
from the CurrentDb object, as in Example 2?

Question 3: Is it really necessary to set these objects to Nothing, as in
Example 1, or close the recordset in Example 2? I thought that the scope of
procedure variables and objects (at least in these Private Subs) is limited
to the current procedure, and that they vanish when the procedure is exited.
I keep hearing that this cleanup is good practice, but what consequence are
we avoiding?

Question 4: Since I'm compiling and creating an MDE file for distribution,
I'm not concerned about the readability of the code in the end. Also, I'm
the only developer likely to work on this application, and I am accustomed
to my own habits. If I can shrink my code throughout like the above example
shows, does it make any difference once Access compiles it, or does the
compiler turn both types of code into the same set of instructions anyway
(like the way it ignores white space and comments)?

I have not been diligent in closing my recordsets in the past, and I have
not noticed any problems coming from it, but I have been curious if that is
what contributes to the increasing size of the mdb file. I'm guessing there
must be pieces of code or table data getting tagged to the end of the file
and not cleared from the buffer until the Compact/Repair command is
executed. I recently stepped through my code and closed all open objects
and set dimensioned object variables to Nothing before exiting subs, and the
file growth continues as before. It's not a problem, as the Compact command
keeps it in check (I have the "Compact on Exit" option set), but it made me
wonder about the above items.

Thanks in advance for any input.

Paul Johnson

 

[Tim Ferguson]

Question 2: Is there any harm in creating the recordset object directly
from the CurrentDb object, as in Example 2?

The CurrentDB() function has been through a number of mutations over serial
versions of Access, many of which had direct consequences for the
programmer. If memory serves, the original version failed to disallow the

CurrentDB().Close

method with startling results! Later versions produced copies of the
database object, duplicate handles and the like. At one time, we were
exhorted to use DBEngine.Workspaces(0).Databases(0) instead of CurrentDB()
at all, and some programmers still do.

One current (as far as I know) problem with using CurrentDB() in a loop is
that subsequent calls return a new handle each time: this causes fatal
errors in loops like

For Each tdf In CurrentDB.TableDefs
...

where the database object would cease to exist on the second call. It
appears that this has never affected the OpenRecordset method but (a)
nobody knows why not, and (b) we'll never know if that will hold true in
version (n+1). Therefore most people work defensively and use a local
variable to hold the database object, and take all references off that.

Hope it helps


Tim F

 

[Paul Johnson]

Okay, call me skeptical. I hadn't had trouble with the loop you mentioned
at the end of your post (I had used it very recently), so I tested making a
"second call" to the CurrentDb.TableDefs collection using the following
simple code in a module, which I called from the immediate window:

Sub test()
Dim t As TableDef
For Each t In CurrentDb.TableDefs
Debug.Print t.Name
Next t
For Each t In CurrentDb.TableDefs
Debug.Print t.Fields(0).Name
Next t
End Sub

I'm not seeing a problem with it. Am I missing something here?

Paul Johnson

 

[Marshall Barton]

Okay, call me skeptical. I hadn't had trouble with the loop you mentioned
at the end of your post (I had used it very recently), so I tested making a
"second call" to the CurrentDb.TableDefs collection using the following
simple code in a module, which I called from the immediate window:

Sub test()
Dim t As TableDef
For Each t In CurrentDb.TableDefs
Debug.Print t.Name
Next t
For Each t In CurrentDb.TableDefs
Debug.Print t.Fields(0).Name
Next t
End Sub

I'm not seeing a problem with it. Am I missing something here?

I think For Each (and With) do things differently than other
constructs, but I've never heard anyone explain how
differently so I get a queasy feeling when I do what that
kind of thing.

I do know that
Set tdf = CurrentDb.TableDefs!tablename
always went of scope before the next line could use the tdf
object.

So, as Tim says, better safe than sorry.

 

[Paul Johnson]

That is peculiar. My shorthand habits have been allowing me to slip through
the cracks, using FOR and WITH constructs (I use them just about every time
it saves space), and I never figured out that if I had tried to SET a
TableDef object through CurrentDb, it would have failed, and I would have
been (even more) thoroughly confused.

I understand "Better safe than sorry", to be sure. I wonder if anyone can
explain what the difference is between the SET variable and the FOR/WITH
construct?

Paul

 

[Dirk Goldgar]

I do know that
Set tdf = CurrentDb.TableDefs!tablename
always went of scope before the next line could use the tdf
object.

The problem there is that, unlike the Recordset object, the TableDef
object doesn't maintain a reference to its parent Database object. So
in the statement you cite, the Database object is created by the
CurrentDb method, the TableDef object is created, and then the Database
object is destroyed at the end of the statement as there is no longer
any reference to it. So *poof*, the TableDef object is no longer valid
because its parent has been destroyed.

The DAO Recordset object has a hidden property, Parent, that maintains a
reference to the object's parent Database, so even if all other
references to that object are destroyed, it lives on until the Recordset
itself is destroyed.

 

[Tim Ferguson]

I'm not seeing a problem with it. Am I missing something here?

Not neccessarily: it's highly version-dependent. The Access team introduces
new bugs^W features as quickly as it gets rid of them, so it's likely that
A2002 and 2003 and so on will all have their own quirks. Even more reason
to program as defensively as possible.

See the other responses for just how complex this gets...

B Wishes


Tim F

 

[Marshall Barton]

I use For Each and With all the time (excellent additions to
the language), but, when I remember, I set the For Each
variable to Nothing after the loop.

As for With, there is no variable to set to Nothing, so I
just try to have faith that are no bugs in End With taking
it out of scope (have not had a problem yet).