plain lazy

Banana Banana@Republic wrote in news:4BFB2414.9050608@Republic:

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.

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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Dirk Goldgar" wrote in
news
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.

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 5/25/10 10:35 AM, David W. Fenton wrote:
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?

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 Banana@Republic wrote in news:4BFC1273.1060408@Republic:

On 5/25/10 10:35 AM, David W. Fenton wrote:
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?

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.

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/