using CLOSE and NOTHING

I am wondering if there is a preferred way of creating objects (if that is
the right word). For example, I am using:

Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connection

I know I could also use:

Dim cnn1 As New ADODB.Connection

I understand both do the same thing? Are there scenarios where one is
preferred
over the other, or simply a personal choice?

Also, I am more interested in the proper use of "close" and "set object to
nothing"
In my above code, at the end I have:

cnn1.Close

Is this all I need to do? Should I also have:

Set cnn1 = Nothing

Does using close without set = nothing leave anything open?

Thanks!

Dion

Originally Posted by Dion

I am wondering if there is a preferred way of creating objects (if that is
the right word). For example, I am using:

Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connection

I know I could also use:
Dim cnn1 As New ADODB.Connection

I understand both do the same thing? Are there scenarios where one is
preferred
over the other, or simply a personal choice?

Also, I am more interested in the proper use of "close" and "set object to
nothing"
In my above code, at the end I have:
cnn1.Close
Is this all I need to do? Should I also have:
Set cnn1 = Nothing
Does using close without set = nothing leave anything open?

Thanks!
Dion

The two line one is preffered but only because it's easier to read

Please Login or Register  to view this content.

You shoul use both .close (first) and set to nothing.
Why?
.close let's vba know the connection is closed and some buffers and error checking is done. But as Microsoft is Microsoft due to a glitch it does not relaase the memory used by this variable ans setting to nothing reeases this chunk of memory.
Setting to nothing without closing can lock up your database becaus a record set wasn't closed

Rule ot thumb:
close and set to nothing objects in the reverse order you've have set them.

so first the recordset(s) and then the database for example.

"Dion" <Dion@discussions.microsoft.com> wrote in message
news:FC3245F6-2263-4771-A161-DA46003BFCD1@microsoft.com...
> I am wondering if there is a preferred way of creating objects (if that is
> the right word). For example, I am using:
>
> Dim cnn1 As ADODB.Connection
> Set cnn1 = New ADODB.Connection
>
> I know I could also use:
>
> Dim cnn1 As New ADODB.Connection
>
> I understand both do the same thing? Are there scenarios where one is
> preferred
> over the other, or simply a personal choice?

No, it bis not just a personal choice.

The latter method has (at least) two disadvantages to the former.

The object is created as soon as it is referenced in code, so it is
impossible to test whether it is not set.
If cnn1 Is Nothing Then
is meaningless, because if it were nothing, the test would create it,
thereby making it something.

It is less efficient, as the VBA effectively tests it for nothing every time
it is referenced, checking to see whether to create it or not.

It has only one advantage that I can see, it is less typing.

> Also, I am more interested in the proper use of "close" and "set object to
> nothing"
> In my above code, at the end I have:
>
> cnn1.Close
>
> Is this all I need to do? Should I also have:
>
> Set cnn1 = Nothing
>
> Does using close without set = nothing leave anything open?

It is a big debatable point that one. Matt Curland maintains that it is a
waste of time to clear object variables like that, but Excel does have a
habit of not quitting if executed from code if there are objects left in
memory (especially implicit objects), so it is probably best to do so, it
doesn't take much effort, and it is aesthetically pleasing <g>