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

"Dion" wrote in message
...
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

Dion Wrote:
I am wondering if there is a preferred way of creating objects (if tha
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 objec
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

Code
-------------------
Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connectio
-------------------


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

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

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

--
Dnere
-----------------------------------------------------------------------
Dnereb's Profile: http://www.excelforum.com/member.php...fo&userid=2618
View this thread: http://www.excelforum.com/showthread.php?threadid=39953