From: Richard on
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
From: Dirk Goldgar on
"Richard" <Richard(a)discussions.microsoft.com> wrote in message
news:8B80452B-F67B-4730-8D40-25F20921F9A2(a)microsoft.com...
> 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)

From: Banana on
On 5/20/10 5:57 AM, Dirk Goldgar wrote:
> "Richard" <Richard(a)discussions.microsoft.com> wrote in message
> news:8B80452B-F67B-4730-8D40-25F20921F9A2(a)microsoft.com...
>> 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.
From: Richard on
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" <Richard(a)discussions.microsoft.com> wrote in message
> > news:8B80452B-F67B-4730-8D40-25F20921F9A2(a)microsoft.com...
> >> 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.
> .
>
From: Tony Toews [MVP] on
"Dirk Goldgar" <dg(a)NOdataSPAMgnostics.com.invalid> 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/