plain lazy
in [General]
|
Prev: Need to normalize some tables after database has been split
Next: Reminder - Microsoft Responds to the Evolution of Community
From: David W. Fenton on 25 May 2010 13:35 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 25 May 2010 13:35 "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 25 May 2010 14:09 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 26 May 2010 17:20
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/ |