From: David W. Fenton on
Banana <Banana(a)Republic> wrote in news:4BFB2414.9050608(a)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/
From: David W. Fenton on
"Dirk Goldgar" <dg(a)NOdataSPAMgnostics.com.invalid> wrote in
news:D9A32526-BD0F-4F76-BEA6-84765691D3EA(a)microsoft.com:

> 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/
From: Banana on
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.

From: David W. Fenton on
Banana <Banana(a)Republic> wrote in news:4BFC1273.1060408(a)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/