A general rule for using methods that create objects

[JGPatrick]

Objects created by invoking methods do not seem to behave like normal objects
in VBA.

Consider that in other programming languages, once you set an equality, you
can use either side of the expression interchangeably.

For instance:

A = B
C = sqrt(A)

will give you the same thing as

A = B
C = sqrt(B)

But consider two examples where this interchangeability is not true in VBA.

You can say:

Set Ws = DBEngine.CreateWorkspace( Arguments ) (ie, A = B)

and then you can say: Workspaces.Append Ws (similar to C = sqrt(A))

but you cannot do: Workspaces.Append DBEngine.CreateWorkspace( Arguments ),
(like saying you can't do C = sqrt(B)). If you try to do this, you will run
into
later problems when you try to use the last item in the Workspaces collection.

In a second example, you can say

Set Dbs = CurrentDb() (ie, A = B)

And then you can: debug.print Dbs.TableDefs(0).Name (like you evaluate
sqrt(A) )

But you cannot Set Tbs = CurrentDB().TableDefs(0) and work with THAT
directly (Like saying you can't do sqrt(B)).

So clearly I need to understand what is going on when you create an object
by executing a method in VBA.

Now in the second example, after reading responses to my earlier posting
called "a fundamental difference between object variables and other
variables", here's my understanding of what the issue is. Let's say the
current default database is the Northwind databse, whose first table is
Customers.

Set Tbs = CurrentDb().TableDefs(0) does NOT say

"Tbs is the Customers table in the Northwind database."

Instead, it says

"Tbs is the first table in the database whose name is written on the
blackboard in
the room marked 'CurrentDb'. "

The problem is that this blackboard gets erased, so that VBA cannot define
Tbs in later statements.

However, I have to say that this is still not that satisfying. Extending the
analogy, the statement

Set Dbs = CurrentDb()

should say

"Dbs is the database whose name is written on the blackboard in the room
marked 'CurrentDb'."

so later references to Dbs should also fail when that blackboard gets erased.
But they do not.

So instead of trying to understand what the difference is between
method-created objects and other objects, I think I will be satisfied with
simply
using the following rule:

Never invoke a method that creates an object, except on the right hand side
of a Set statement.

ie, for any object O that has a method M that creates an object, the only
place you should ever see O.M is in statements of the form

Set obj = O.M( Arguments ).

And if you have a function F() that has been defined via

Function F( Arguments ) As some kind of object

the only place you should ever see F( Arguments ) is in
statements of the form

Set obj = F( Arguments )

I would appreciate it if someone would be able to supply an alternative rule
which is less conservative but is also guaranteed to always work.

 

[David C. Holley]

When you're dealing with OBJECTS. A doesn't equal B. They are two completely
different objects, although they may be of the same type. When you create an
Object Variable, memory is set asside for that specific instance of the
object. If you then create a second object, memory is allocated for a second
instance of the object so they may be identical, but they are not the same.
If I'm cloned, is it true that David = David's Clone and David's Clone =
David? Yes and no, we are identical but not the same entity.

If you create two recordset objects that point to the same records using the
exact same SQL statement and then move to the 5th record in one of them, the
other will still be at its original position because you have acted on one
object, but not the other.

When you're working a regular variables, their simply storing a value so YES
A = B and B = A if you're dealing with VALUES.

 

[David C. Holley]

Missed this.

The equals sign takes on a different meaning when used with the SET
keyworld. When used with the SET keyword, you are essentially saying this
variable GETS a copy of this object. Without the SET keyword, your checking
for equality of value. Some languages feature the single equals versus the
double equals which is effectively the same concept.

There are some instances where you have to essentially walk the object
heirarchy.

 

[vanderghast]

With


Dim a As object
Dim b As object

Set a = ....
Set b = a

Than "a" and "b" refers to the same object, no copy of the object is made.


-------------------------
Option Explicit
Public Sub A_B()

Dim a As Collection
Dim b As Collection

Set a = New Collection
Set b = a

a.Add "one"
a.Add "two"

Debug.Print b.Count

End Sub
---------------------------


If a copy was made, b.Count would be 0.


Vanderghast, Access MVP

 

[vanderghast]

Workspaces.Append Ws

is appending to a collection (the collection of Workspaces), it is not
transforming Ws in any manner, so it is not like Sqrt(A), but it sends the
reference to the collection (and COM increases the reference count by one).
In fact, the reference count has to be increased so that if Ws is further
set to nothing, the object is NOT to be destroyed, since the collection may
still need that object. Easier, maybe, to see what happens with an example
(note that user defined collection, in VBA are origin 1, while native
collections are origin 0, though):


============================
Option Compare Database
Option Explicit

Public Sub A_B( )

Dim a As Collection
Dim b As Collection
Set a = New Collection

a.Add "one"
a.Add "two"


Set b = New Collection

b.Add a ' <- 'send' object a to collection b

Set a = Nothing ' <- and destroy a

Debug.Print "b.count", b.Count
Debug.Print "b(1).Count", b(1).Count

End Sub
=========================

So, even if "a" is destroyed, set to nothing, it is ONLY the 'handle to the
bag' which is destroyed, NOT the bag ! I mean, it can be easier to consider
that


Dim a As Object


defines a HANDLE (pointer, reference, whatever) while

Set a = ...

ties that handle to a BAG (whatever is at the right of the = sign, in my
example, an empty collection ).


Setting the handle to nothing only destroy the handle, not the bag. You
cannot destroy the bag, directly, only if the reference count (only if no
handle is now tied to the bag) would COM destroy the bag. In our case, since
there is another handle to the bag (an item from collection b), the bag
still exists. You can add


Debug.Print b(1)(2)

to get "two" printed out, which proves that the collection initially tied to
handle "a" still exists and is quite well.


Now, the reference count (which is at the base of COM memory management) has
some quirks. One of them is about what occur if you create a temporary
object 'in a line' and don't capture it, such as:

CurrentDb.OpenRecordset("SELECT COUNT(*) FROM table1").Fields(0).Value

where you create a recordset! That recordset is not DIM anywhere,
explicitly, in your code, but one is created none the less, with
OpenRecordset, and you even get some data out of it. But how the COUNT
reference count is handled? Clearly, the reference count has to be not zero
for 'some time', and that some time is not a timer, but how the VBA code is
compiled, but someone may say that the recordset exists for the 'line' of
VBA code, whatever that single line of VBA code generate one or many lines
of compiled instructions. Same thing occur for CurrentDb itself! And one way
to be 'safe' it is still 'alive' (ie, that the reference returned by
CurrentDb is still pointing to a valid 'bag') is to force COM to keep a not
zero reference count, ie:

Dim db As Database
...
Set db= CurrentDb


then COM won't destroy the bag because there is still an handle tied to it,
a little bit like, in our presentation code
Set a = nothing
destroy the handle, but not the bad of data, here, the handle of CurrentDb
may well be killed 'at the end of the line', but the bag it was tied to
won't be killed, because another handle, db, is still tied to it.




Well, I hope that makes sense.


Vanderghast, Access MVP

 

[JGPatrick]

David, Vanderghast,

Thank you both so much for those replies. You've both helped make
object-oriented programming more understandable to me.

As for the examples that were causing me confusion, the analogy of methods
as creating bags and references as creating handles is crystal clear, and
answers all of my questions.

The bottom-line for the non-expert is, to be safe, use assignment statements
to create references for objects.