Jim Dettman
jimdettman at verizon.net
Tue Aug 11 10:44:02 CDT 2009
Dan, FWIW the difference between the two and the pros/cons have been in the ADH since 95 I believe (97 for sure). I've also seen that function he wrote floating around for a while now. I've always used CurrentDB() myself because it's always consistent with the UI and at one point Microsoft was talking about allowing multiple DB's to be opened in the Access UI. In fact between Access 2 and 95, Microsoft said CurrentDB() was to be phased out and then went back to it, so that seemed likely. Never came to pass though. I don't refer to dbEngine all that often directly, unless I'm working with a remote database. Probably stupid not to use that function though and get the best of both worlds... Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters Sent: Tuesday, August 11, 2009 10:05 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] dbEngine(0)(0) - was Re: OpenRecordSet question 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 > ------------ -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com