plain lazy

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

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 said:
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....
Click to expand...


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.
 
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.
Click to expand...

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 said:
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.
Click to expand...

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/
 
Why is it better? Save time?

Tony
Click to expand...

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 said:
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." ;)
Click to expand...

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/
 
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
Click to expand...

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 said:
Now I'm thinking I need to re-take grammar school and work on my reading
comprehension.
Click to expand...

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 said:
Why is it better? Save time?
Click to expand...


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.
 
Banana said:
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.
Click to expand...

Are we certain there aren't issues with CurrentProject.Connection
similar to those with DBEngine(0)(0) vs. CurrentDB()? That is, is it
more like the former than the latter?
 
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.
Click to expand...

Having a cached object variable also means that if you want to
change the connection you're using requires changing code in only
one place.
 
Are we certain there aren't issues with CurrentProject.Connection
similar to those with DBEngine(0)(0) vs. CurrentDB()? That is, is it
more like the former than the latter?
Click to expand...

Well, it's exposed as a property of CurrentProject that returns a
ADODB.Connection object, unlike CurrentDb which is a function that
returns a DAO.Database or DBEngine(0)(0) which returns an item of its
databases collection for default workspace.

I would think the analogy would be that Connection = Command Prompt and
DBEngine(0)(0) = opening a word document that is already opened by other
instance of Word but hasn't saved its edits. I can't be certain, but I
have a suspicion that collections in DAO are maintained in memory but
actually are just copies of what is actually written to the file which
is why we can create a new tabledef but if we refer to the TableDefs
collection without refreshing the collection, we won't find the new
table. This doesn't happen with ADODB.Connection simply because all it
does is send command and read the return values, just like a command
prompt would do. It has no collections or objects to maintain and it's
the provider's responsibility to answer the command with most current
data accurately.

So, I don't think we really can say CurrentProject.Connection is like
DBEngine(0)(0) or CurrentDb() - it's something entirely different. Come
to think of it, there aren't any collections for Connections, Commands
and Recordsets in ADO.
 
Banana said:
Well, it's exposed as a property of CurrentProject that returns a
ADODB.Connection object, unlike CurrentDb which is a function that
returns a DAO.Database or DBEngine(0)(0) which returns an item of
its databases collection for default workspace.

I would think the analogy would be that Connection = Command
Prompt and DBEngine(0)(0) = opening a word document that is
already opened by other instance of Word but hasn't saved its
edits. I can't be certain, but I have a suspicion that collections
in DAO are maintained in memory but actually are just copies of
what is actually written to the file which is why we can create a
new tabledef but if we refer to the TableDefs collection without
refreshing the collection, we won't find the new table. This
doesn't happen with ADODB.Connection simply because all it does is
send command and read the return values, just like a command
prompt would do. It has no collections or objects to maintain and
it's the provider's responsibility to answer the command with most
current data accurately.

So, I don't think we really can say CurrentProject.Connection is
like DBEngine(0)(0) or CurrentDb() - it's something entirely
different. Come to think of it, there aren't any collections for
Connections, Commands and Recordsets in ADO.
Click to expand...

I think you're missing the point.

DBEngine(0)(0) always has to have collections refreshed each time
you use it (if you've been adding/altering the contents of any
collections), so there's not much difference in that regard between
using it directly or caching it.

CurrentDB() on the other hand, refreshes the collection when it's
called, but if you cache a reference created from it, the
collections in the cached variable remain unrefreshed until you ask
for them to be refreshed (just like with DBEngine(0)(0)).

My comparison to CurrentProject.Connection is a question about
collections being refreshed as one example of how it might behave
differently if called repeatedly versus being cached. CurrentDB() is
vastly slower than a cached reference of DBEngine(0)(0), and if
CurrentProject.Connection has similar overhead, there might be good
reasons to use a cached reference instead of calling it repeatedly.

So, it was an analogy, because I wondered if there are differences
between what you get calling it directly and what you get using a
cached connection.
 
Back
Top