When is NOTHING better than nothing?

[Lauren Quantrell]

OK, so I know to:

Dim RS As ADODB.Recordset
Set RS = CurrentProject.Connection.Execute("EXEC " & "mySPROCname")

'make all sorts of cool stuff happen here...

RS.Close
Set RS = Nothing

and I also:

Dim cmd As ADODB.command
Set cmd = Nothing

Dim prm As ADODB.Parameter
Set prm = Nothing

Dim ctl As Control
Set ctl = Nothing

Dim objCbo As Object
Set objCbo = Nothing

I have always assumed these are necessary, but am I missing anything?

Thanks,
lq

[John Mishefske]

Lauren Quantrell wrote:

OK, so I know to:

Dim RS As ADODB.Recordset
Set RS = CurrentProject.Connection.Execute("EXEC " & "mySPROCname")

'make all sorts of cool stuff happen here...

RS.Close
Set RS = Nothing

and I also:

Dim cmd As ADODB.command
Set cmd = Nothing

Dim prm As ADODB.Parameter
Set prm = Nothing

Dim ctl As Control
Set ctl = Nothing

Dim objCbo As Object
Set objCbo = Nothing

I have always assumed these are necessary, but am I missing anything?

Thanks,
lq

Its far more important to ensure you close those Recordsets since they are a popular
source of memory leaks. As a rule I "Set x = Nothing" for anything I set but the need for
that is heavily debated here. At most it means a reference is leaked. YMMV.

--
'---------------
'John Mishefske
'---------------

[Lauren Quantrell]

But...

Dim str as String
Set str = Nothing

Dim x as Integer
Set x = Nothing

???

[John Mishefske]

Lauren Quantrell wrote:

But...

Dim str as String
Set str = Nothing

Dim x as Integer
Set x = Nothing

???

No..no.. You don't need to use the Set with these data types. Objects use the Set command
to allocate memory and then create a reference to that memory space. The basic data types
do not require this. Instead you'd use:

Dim str as String
str = Nothing

Dim x as Integer
x = Nothing

But objects (like Recordsets or Forms) need to be Set. Check the on-line help for more info.

--
'---------------
'John Mishefske
'---------------

[lylefair]

I expect that I have created more ADODB objects than most, perhaps any,
here having used them by choice since 1999.
I do not close ADODB objects. I do not set them to nothing. My work is
calculation intensive. In it, the creation of ADODB recordsets and
their use can be repeated many many times.
I have never experienced a problem from my failure to do so; none of my
clients has reported a problem that can be traced to such a cause.

[John Mishefske]

John Mishefske wrote:

Lauren Quantrell wrote:

But...

Dim str as String
Set str = Nothing

Dim x as Integer
Set x = Nothing

???

No..no.. You don't need to use the Set with these data types. Objects
use the Set command to allocate memory and then create a reference to
that memory space. The basic data types do not require this. Instead
you'd use:

Dim str as String
str = Nothing

Dim x as Integer
x = Nothing

But objects (like Recordsets or Forms) need to be Set. Check the on-line
help for more info.

I should have looked at this closer. You only assign Nothing to an Object. A better
example would be:

Dim str as String
str = "some text"

Dim x as Integer
x = 6

--
'---------------
'John Mishefske
'---------------

[Terry Kreft]

Using the close method frees system resources such as data and locks;
setting the object to nothing releases the memory associated with the
object.

Having had experience of failure to close a COM object causing an
application to stay in memory I always close and set to nothing (as
appropriate) any objects I create.

It's entirely up to you whose advice you follow but I can't see the problem
in an extra couple of code lines per object created, as opposed to spending
half a day trying to track down an obscure bug caused by failure to release
a COM object properly.
--
Terry Kreft

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

OK, so I know to:

Dim RS As ADODB.Recordset
Set RS = CurrentProject.Connection.Execute("EXEC " & "mySPROCname")

'make all sorts of cool stuff happen here...

RS.Close
Set RS = Nothing

and I also:

Dim cmd As ADODB.command
Set cmd = Nothing

Dim prm As ADODB.Parameter
Set prm = Nothing

Dim ctl As Control
Set ctl = Nothing

Dim objCbo As Object
Set objCbo = Nothing

I have always assumed these are necessary, but am I missing anything?

Thanks,
lq

[MLH]

<snip>

It's entirely up to you whose advice you follow but I can't see the problem
in an extra couple of code lines per object created, as opposed to spending
half a day trying to track down an obscure bug caused by failure to release
a COM object properly.

On the subject of COM objects, I know nothing (and NOTHING).
Therefore,

Dim MyKnowledge As ADODB.Recordset
Set MyKnowledge = CurrentProject.Connection.Execute("EXEC " &
"mySPROCname")

- AND -

Set MyKnowledge = Nothing

Are identical for me. Would like to know more, starting with "What are
COM objects? -and- How do I make them in A97?" Would appreciate
a point in the right direction. Thx.

[Randy Harris]

"lylefair" <ly***********@aim.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I expect that I have created more ADODB objects than most, perhaps any,
here having used them by choice since 1999.
I do not close ADODB objects. I do not set them to nothing. My work is
calculation intensive. In it, the creation of ADODB recordsets and
their use can be repeated many many times.
I have never experienced a problem from my failure to do so; none of my
clients has reported a problem that can be traced to such a cause.

Same here. I have been using ADODB objects extensively for nearly as long.
Ever since A2K hit the streets. I've gotten away from setting them to
nothing and have been unable to detect any sort of problem. The one
precaution that I still employ is to always close recordset objects. Some
of the users leave their app logged in all day, creating thousands of
recordsets. No problems.

[David W. Fenton]

"Lauren Quantrell" <la*************@hotmail.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

But...

Dim str as String
Set str = Nothing

Dim x as Integer
Set x = Nothing

These are not objects.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

John Mishefske <jm**********@SPAMyahoo.com> wrote in
news:pM****************@tornado.rdc-kc.rr.com:

Lauren Quantrell wrote:

But...

Dim str as String
Set str = Nothing

Dim x as Integer
Set x = Nothing

No..no.. You don't need to use the Set with these data types.
Objects use the Set command to allocate memory and then create a
reference to that memory space. The basic data types do not
require this. Instead you'd use:

Dim str as String
str = Nothing

Dim x as Integer
x = Nothing

But objects (like Recordsets or Forms) need to be Set. Check the
on-line help for more info.

Nothing is used for items that you use SET to initialize. THat means
objects.

If you try to use "= Nothing" with non-objects, you get a compile
error.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Lauren Quantrell" <la*************@hotmail.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

OK, so I know to:

Dim RS As ADODB.Recordset
Set RS = CurrentProject.Connection.Execute("EXEC " &
"mySPROCname")

'make all sorts of cool stuff happen here...

RS.Close
Set RS = Nothing

and I also:

Dim cmd As ADODB.command
Set cmd = Nothing

Dim prm As ADODB.Parameter
Set prm = Nothing

Dim ctl As Control
Set ctl = Nothing

Dim objCbo As Object
Set objCbo = Nothing

I have always assumed these are necessary, but am I missing
anything?

Lyle says that it's not necessary with ADO, because the memory leaks
between VBA and ADO don't exist as they do between DAO and VBA.

I don't use ADO, so I can't confirm or deny his assertion.

If I *did* use ADO, I think I'd do manual cleanup, anyway, just in
case.

Now, some of those things are neither ADO nor DAO, such as Control
and Object. Object, I"d definitely set to Nothing, since it can be
any number of things and is very likely to be entirely external to
Access (as opposed to DAO and ADO objects, which, while external to
Access are known by Access; i.e., Access is designed to work well
with them).

Control is different, as it's entirely Access. But Michael Kaplan
once advised in this newsgroup that it's potentially possible that
looping through the controls collection could leave an implicit
reference to a control unreleased, so I always do this:

Dim ctl As Control

For Each ctl In Me.Controls
[do whatever]
Next ctl
Set ctl = Nothing

So, whenever I walk a collection using a specific variable type
(tdf, qdf, ctl, etc.) I set it to nothing, because each iteration of
the loop is going to do an implicit SET = operation.

The exception is when I use a generic variable type:

Dim varItem as Variant

For Each varItem In Me!lstMyList.ItemsSelected
[do whatever]
Next varItem

In that case, the variant data type is one that is never SET to a
value, so there is no need for setting it to Nothing, since it can't
hold an implicit object reference.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[John Mishefske]

David W. Fenton wrote:

Nothing is used for items that you use SET to initialize. THat means
objects.

If you try to use "= Nothing" with non-objects, you get a compile
error.

It was a cut and paste error. I immediately followed up that post with a correction.

--
'---------------
'John Mishefske
'---------------

[John Mishefske]

David W. Fenton wrote:

"Lauren Quantrell" <la*************@hotmail.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

But...

Dim str as String
Set str = Nothing

Dim x as Integer
Set x = Nothing

These are not objects.

It was a cut and paste error. I immediately followed up that post with a correction.

--
'---------------
'John Mishefske
'---------------

[Terry Kreft]

The best book I have read to explain COM is "Programming Distributed
Applications With Com & Microsoft Visual Basic 6.0 (Programming/Visual
Basic)", there is a later book which deals with COM+ but the explanation of
the basics is a bit more sketchy.

You can pick it up from Amazon ...
http://www.amazon.com/gp/product/157...books&v=glance

at a ridiculous $1.50.

--
Terry Kreft

"MLH" <CR**@NorthState.net> wrote in message
news:0u********************************@4ax.com...

<snip>

It's entirely up to you whose advice you follow but I can't see the
problem
in an extra couple of code lines per object created, as opposed to
spending
half a day trying to track down an obscure bug caused by failure to
release
a COM object properly.

On the subject of COM objects, I know nothing (and NOTHING).
Therefore,

Dim MyKnowledge As ADODB.Recordset
Set MyKnowledge = CurrentProject.Connection.Execute("EXEC " &
"mySPROCname")

- AND -

Set MyKnowledge = Nothing

Are identical for me. Would like to know more, starting with "What are
COM objects? -and- How do I make them in A97?" Would appreciate
a point in the right direction. Thx.

[David W. Fenton]

John Mishefske <jm**********@SPAMyahoo.com> wrote in
news:fk****************@tornado.rdc-kc.rr.com:

David W. Fenton wrote:

Nothing is used for items that you use SET to initialize. THat
means objects.

If you try to use "= Nothing" with non-objects, you get a compile
error.

It was a cut and paste error. I immediately followed up that post
with a correction.

Maybe so, but it raised a question that I thought was worth
answering, anyway.

Who's to say that someone encountering your post in the Google
Groups archive will see the correction?

I didn't mean my post as any criticism of you, certainly.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[John Mishefske]

David W. Fenton wrote:

John Mishefske <jm**********@SPAMyahoo.com> wrote in
news:fk****************@tornado.rdc-kc.rr.com:

David W. Fenton wrote:

Nothing is used for items that you use SET to initialize. THat
means objects.

If you try to use "= Nothing" with non-objects, you get a compile
error.

It was a cut and paste error. I immediately followed up that post
with a correction.

Maybe so, but it raised a question that I thought was worth
answering, anyway.

Who's to say that someone encountering your post in the Google
Groups archive will see the correction?

I didn't mean my post as any criticism of you, certainly.

I agree. I should have taken a minute to review my post before sending.

--
'---------------
'John Mishefske
'---------------

[MLH]

A buck-n-a-half! Now that's decent.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Thu, 20 Oct 2005 10:06:48 +0100, "Terry Kreft"
<te*********@mps.co.uk> wrote:

The best book I have read to explain COM is "Programming Distributed
Applications With Com & Microsoft Visual Basic 6.0 (Programming/Visual
Basic)", there is a later book which deals with COM+ but the explanation of
the basics is a bit more sketchy.

You can pick it up from Amazon ...
http://www.amazon.com/gp/product/157...books&v=glance

at a ridiculous $1.50.