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

I deleted the first few posts of this thread but just opened the one below so I hope I'm not missing the point.

 

This topic is one of the first threads I posted to when I first joined AccessD in the latter half of the 90's.  In those days I was very concerned with performance and network load as I was working in a crippled environment.  I have little use for CurrentDb as it is really slow compared to the alternatives.

 

I've written a function that returns a pointer to a database object.  It uses currentDb to create the initial object and then stores a reference in a static variable which is used in subsequent calls.

 

Public Function fnDB(Optional blnRefresh As Variant) As DAO.Database
    On Error GoTo ErrorHandler
    
    Static db As DAO.Database
    Dim lngi As Long

 

    If IsMissing(blnRefresh) Then
        lngi = Len(db.Name)
    Else
        Set db = CurrentDb
    End If

 

ExitRoutine:
    On Error Resume Next
    Set fnDB = db
    Exit Function
ErrorHandler:
    Set db = CurrentDb
    'Resume 0
    Resume ExitRoutine
End Function

 

I ran a timing test of 10,000 iterations of setting a reference to a database object.

 

The above fnDb ran in an average of 4 milliseconds over 20 runs;

DBEngine(0)(0) ran in 9 milliseconds;

CurrentDb took 239235 milliseconds, just shy of 4 minutes in a single test.

 

The fnDB procedure has an optional argument to force a refresh should a need arise to refresh database object collections.  I have a few saved QueryDefs where a user can dynamically change the .SQL property in his copy of the front end but that does not require a refresh.  This fnDB procedure has not been revised in over 12 years as I don't make changes to database objects that require a refresh of those objects.  As yet, I haven't needed to use the the optional parameter.  Note that I don't test the value of the parameter.  Sending in anything forces a refresh regardless of the datatype or value.

Ciao

Jürgen Welz

Edmonton, Alberta

jwelz at hotmail.com

 
> Date: Mon, 10 Aug 2009 08:11:58 -0400
> From: jwcolby at colbyconsulting.com
> To: accessd at databaseadvisors.com
> 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

_________________________________________________________________
Stay on top of things, check email from other accounts!
http://go.microsoft.com/?linkid=9671355