[AccessD] dbEngine(0)(0) - was Re: OpenRecordSet question

And this method is exactly the method I ended up using.  In my framework I have that exact code 
(with my own names of course) but basically check if a database pointer contains anything, then if 
not sets it, and always returns that db reference.  It is extremely fast.

I didn't do so based on any superior knowledge of the ins and outs of CurrentDb vs dbEngine(0)(0) (I 
don't have any superior knowledge about much of anything) but rather just because I always used 
CurrentDb and it was slow, and I decided to just use a "static" single reference to it.

It is nice to know that cleanup is not required however (no hanging db based on that).

John W. Colby
www.ColbyConsulting.com


Dan Waters wrote:
> John,
> 
> This is a discussion (or slight rant) by Michael Kaplan on DBEngine(0)(0)
> vs. CurrentDB.
> 
> In my databases, I never change collections so I don't need CurrentDB.
> 
> Also, he says that DBEngine(0)(0) is 5000 time faster than CurrentDB.  I did
> a read looping test once and my result was that DBEngine(0)(0) was exactly 8
> times faster than CurrentDB.  I use many recordsets and I do see a
> performance improvement using DBEngine(0)(0) over CurrentDB.
> 
> http://groups.google.com/group/comp.databases.ms-access/msg/9fe98bb5d7cba5ea
> ?hl=en&
> 
> Dan
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Monday, August 10, 2009 7:12 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] dbEngine(0)(0) - was Re: OpenRecordSet question
> 
>  >     On the other hand, if  db1 and db2  point to DbEngine(0)(0), latest
> status resulting from 
> refresh of tabledefs collection via db1 is directly seen by db2 as well.
> There is no need for a 
> separate refresh via db2.  The statement  db1 Is db2  returns True.
> 
> And therein lies the definition of when CurrentDb "should" be used?  When a
> view of the previous 
> state is required?
> 
> I know that I have always just used
> 
> dim db ad dao.database
> 	set db = currentdb
> 
> I do this in forms, functions, classes, wherever I need to use a db object
> to execute sql or open a 
> recordset or...
> 
> But WHY?????  Because that is the method I learned and so that is what I
> use.
> 
> However it does not seem the appropriate method of getting a database
> object, since it has a high 
> cost (time to create).  In all of these cases I do not care to know the
> previous state of the 
> database "skeleton" after doing whatever it is I am doing.  In fact in the
> vast majority of cases I 
> am not modifying the skeleton at all, just doing something data related.
> 
> So doesn't it seem appropriate to use dbengine (0)(0) to get my db pointer
> in these cases?  In fact 
> it seems necessary if I want any other open DB pointers to automatically see
> correct record counts 
> etc based on some other DB instance performing some append / delete.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Gustav Brock wrote:
>> Hi A.D.
>>
>> That makes sense. 
>>
>> However, I've never had a need to have a db1 and a db2 referring to the
> same database - only two different databases. But who knows, one day maybe?
>> /gustav
>>
>>
>>>>> adtp at airtelmail.in 10-08-2009 10:31 >>>
>> Gustav,
>>
>>      It seems that in order to get the revised status, say count of
> records after executing an append query, refresh method of pertinent
> collection needs to be called anyway, whether the pointer pertains to
> CurrentDb or DbEngine.
>>     If db1 and db2 are two object variables pointing to CurrentDb, and an
> append query has been executed via db1, latest status obtained by refresh of
> tabledefs via db1 (say  db1.TableDefs.Refresh) is not transparent to db2.
> For db2 also to see the uptodate position, explicit statement like
> db2.TableDefs.Refresh becomes necessary.  The statement  db1 Is db2  returns
> False.
>>     On the other hand, if  db1 and db2  point to DbEngine(0)(0), latest
> status resulting from refresh of tabledefs collection via db1 is directly
> seen by db2 as well. There is no need for a separate refresh via db2.  The
> statement  db1 Is db2  returns True.    
>> Best wishes,
>> A.D. Tejpal
>> ------------
>