plain lazy

hi

Instead of Dim and Set a connection, close and set to nothing, can I just use

currentproject.connection.execute "code"

what will be the consequences and will there be a memory set somewhere? Or
should I go the long way....

many thanks in advance for your help.

Richard

"Richard" wrote in message
...
hi

Instead of Dim and Set a connection, close and set to nothing, can I just
use

currentproject.connection.execute "code"

what will be the consequences and will there be a memory set somewhere? Or
should I go the long way....


There should be no consequences. If you're going to use the connection more
than once in a procedure, it's better to define and set a Connection object,
but if you're only going to use it once, you don't need to.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

On 5/20/10 5:57 AM, Dirk Goldgar wrote:
"Richard" wrote in message
...
hi

Instead of Dim and Set a connection, close and set to nothing, can I
just use

currentproject.connection.execute "code"

what will be the consequences and will there be a memory set
somewhere? Or
should I go the long way....


There should be no consequences. If you're going to use the connection
more than once in a procedure, it's better to define and set a
Connection object, but if you're only going to use it once, you don't
need to.


As an alternative where I need what is called "lazy instantiation", I
usually wrap the call to a self-healing property:

code
Property Get MyConn() As ADODB.Connection

Static c As ADODB.Connection

Select Case True
Case c Is Nothing, c.State = adStateClosed
Set c = New ADODB.Connection
c.ConnectionString = ...
...
End Select

Set MyConn = c

End Property
/code

I can still dispose of MyConn by doing this:

code
Set MyConn = Nothing
/code

Which usually is only called at the end of session (e.g. Access is about
to close down for example). This approach allows me to reference MyConn
without needing to worry whether it's open and active or set up a
variable. This works very well especially when I'm uncertain which
procedure that depends on this connection will be called first or even
if at all. This is also safer than a global variable because if an error
reset the state or the connection gets closed, the subsequent call to
the property will heal. (BTW, it uses a Select Case instead of If/Then
with a Or - Doing a Or eagerly evaluates both cases which is impossible
and will cause an error - Select Case allows to evaluate both
possibility sequentially without an error/need to handle the error)

HTH.

Hey guys

Thanks for taking time to help and advise.

Help appreciated
Richard

"Banana" wrote:

On 5/20/10 5:57 AM, Dirk Goldgar wrote:
"Richard" wrote in message
...
hi

Instead of Dim and Set a connection, close and set to nothing, can I
just use

currentproject.connection.execute "code"

what will be the consequences and will there be a memory set
somewhere? Or
should I go the long way....


There should be no consequences. If you're going to use the connection
more than once in a procedure, it's better to define and set a
Connection object, but if you're only going to use it once, you don't
need to.


As an alternative where I need what is called "lazy instantiation", I
usually wrap the call to a self-healing property:

code
Property Get MyConn() As ADODB.Connection

Static c As ADODB.Connection

Select Case True
Case c Is Nothing, c.State = adStateClosed
Set c = New ADODB.Connection
c.ConnectionString = ...
...
End Select

Set MyConn = c

End Property
/code

I can still dispose of MyConn by doing this:

code
Set MyConn = Nothing
/code

Which usually is only called at the end of session (e.g. Access is about
to close down for example). This approach allows me to reference MyConn
without needing to worry whether it's open and active or set up a
variable. This works very well especially when I'm uncertain which
procedure that depends on this connection will be called first or even
if at all. This is also safer than a global variable because if an error
reset the state or the connection gets closed, the subsequent call to
the property will heal. (BTW, it uses a Select Case instead of If/Then
with a Or - Doing a Or eagerly evaluates both cases which is impossible
and will cause an error - Select Case allows to evaluate both
possibility sequentially without an error/need to handle the error)

HTH.
.

"Dirk Goldgar" wrote:

If you're going to use the connection more
than once in a procedure, it's better to define and set a Connection object,
but if you're only going to use it once, you don't need to.

Why is it better? Save time?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

On 5/24/10 12:38 PM, Tony Toews [MVP] wrote:
"Dirk wrote:

If you're going to use the connection more
than once in a procedure, it's better to define and set a Connection object,
but if you're only going to use it once, you don't need to.

Why is it better? Save time?

Tony

Simply because Connection is a relatively expensive object to
create/destroy - it's cheaper to keep a existing connection alive and
re-use it than it is to close and re-open connection on the demand.
Granted, one could go too far and end up holding dead connection which
is bad for the server but that's why we have the saying, "moderation in
everything."

Banana Banana@Republic wrote:

If you're going to use the connection more
than once in a procedure, it's better to define and set a Connection object,
but if you're only going to use it once, you don't need to.

Why is it better? Save time?

Tony

Simply because Connection is a relatively expensive object to
create/destroy - it's cheaper to keep a existing connection alive and
re-use it than it is to close and re-open connection on the demand.
Granted, one could go too far and end up holding dead connection which
is bad for the server but that's why we have the saying, "moderation in
everything."

But if it's the currentproject connection it should always be present
therefore next to no time/resources to create. Just use the object.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

On 5/24/10 2:29 PM, Tony Toews [MVP] wrote:
But if it's the currentproject connection it should always be present
therefore next to no time/resources to create. Just use the object.

Tony

Now I'm thinking I need to re-take grammar school and work on my reading
comprehension. I thought Dirk's response was to do with opening a
connection against a linked source, not CurrentProject.Connection, but
he never said anything about linked source.

Yes you're right - creating a object to do what
CurrentProject.Connection does not really make sense. At least, one
could just use a With block to cut on fairly verbose length of the
invocation.

Banana Banana@Republic wrote:

But if it's the currentproject connection it should always be present
therefore next to no time/resources to create. Just use the object.

Now I'm thinking I need to re-take grammar school and work on my reading
comprehension.

Happens to me a fair bit too. Just search for my name and look for
keywords such as Oops or sorry or Duhhh. Hehehe

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

"Tony Toews [MVP]" wrote in message
news
"Dirk Goldgar" wrote:

If you're going to use the connection more
than once in a procedure, it's better to define and set a Connection
object,
but if you're only going to use it once, you don't need to.

Why is it better? Save time?


It's not a big deal, but I see two reasons:

1. Dereferencing cost. Although this is purely theory, I figure there's a
cost for ever "dot" you traverse. "CurrentProject.Connection.Execute" = two
dots. "objConnection.Execute" = one dot. But it costs a dot for "Set
objConnection = CurrentProject.Connection", so it's not worth doing for a
single use of the object.

2. Code simplicity. The fewer words in the code, the easier it is to read
and maintain. That's another reason to favor "With" blocks over declaring
and setting an object variable.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)