Syntax for New and Set with ADODB.Connection

[Max Moor]

Hi All,
When declaring a recordset, I declare the variable at the top of the
function, then, just before it's first use, I instanciate it:

Set rst = New ADODB.Recordset

I want to do the same thing with an ADODB.Connection. What I have in
code now, is a declaration at the top:

Dim ADOCon As New ADODB.Connection

Then before it's first use...

Set ADOCon = CurrentProject.Connection

This instanciates at declaration, though. I think I want to change this
to is...

Dim ADOCon As ADODB.Connection
...
...
...
Set ADOCon = New ADODB.Connection
Set ADOCon = CurrentProject.Connection

First off, does this look correct? Secondly, is there a way to combine
these staements into one? I'm not thinking that would buy me much. I'm
just curious.

- Max

 

[Douglas J. Steele]

You shouldn't need the Set ADOCon = New ADODB.Connection statement at all:
Set ADOCon = CurrentProject.Connection
should be adequate.

You'd only need Set ADOCon = New ADODB.Connection if you then assigned
values to its properties.

 

[Graham Mandeno]

Hi Max

Dim ADOCon As ADODB.Connection
...
Set ADOCon = New ADODB.Connection
Set ADOCon = CurrentProject.Connection

The second line here is unnecessary, and in fact, counterproductive. You do
not want to create a new connection, but simply to set a reference pointer
to an existing one. If you include the second line, you create a new
connection and then dereference it by pointing its pointer somewhere else.
This implicitly destroys the connection object you have just created.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Max Moor]

You shouldn't need the Set ADOCon = New ADODB.Connection statement at
all: Set ADOCon = CurrentProject.Connection
should be adequate.

You'd only need Set ADOCon = New ADODB.Connection if you then assigned
values to its properties.

Cool. Thanks Doug

 

[Max Moor]

Hi Max


The second line here is unnecessary, and in fact, counterproductive.
You do not want to create a new connection, but simply to set a
reference pointer to an existing one. If you include the second line,
you create a new connection and then dereference it by pointing its
pointer somewhere else. This implicitly destroys the connection object
you have just created.

Thanks Graham.