[AccessD] OpenRecordSet question

Max,

That is not exactly what he means.

The database object contains a whole slew of things like .tabledefs() and .QueryDefs() collections 
etc.  When you dim a database object and set a reference to a database instance using CurrentDb, it 
creates a snapshot of the state of the database at that instance in time.  Suppose that you create 
TWO db variables, db1 and db2 and set each to currentdb.  Now delete a table using the 
db1.tabledefs() object to do so.  The db2.Tabledefs will continue to show all the table objects, 
even though one of them no longer exists because it was deleted by db1 object.

Currentdb returns a SNAPSHOT of the database at a specific point in time.  Currentdb APPARENTLY 
builds up a database object, populating all of the various collections (tabledefs(), QueryDefs() 
etc) with COPIES of the actual objects.  To my knowledge the "copy" goes to the physical 
description, NOT the data contained in a table for example.

Thus, for example, you could:

dim db1 as dao.database
	set db1 = currentdb

Now you could delete every table and query in the database and still use DB1 to see what they were 
and even dim recordsets for tables or queries that no longer exist, iterate said tables fields() 
collections, see what the data types for each field WERE etc.  However you could not OPEN the table 
or query (attempt to see the data) because the actual table is now missing.  The db object that you 
obtained with the currentdb object is a picture of the "skeleton" of the database, at the instant 
that the set statement occurred.

The time to open a Currentdb object can be quite onerous.  Try timing the set db = currentdb in a 
loop for a complex database and you will discover that it can take a long damned time, I have 
observed as much as a quarter of a second.  The reason is obvious if you think about the above 
concept.  The set statement has to somehow create the entire skeleton of the database (make a 
picture).  OTOH DbEngine (0)(0) simply grabs a pointer to the actual database, no overhead at all.

I used the following code to time the two methods:

Const clngMaxLoop As Long = 1000

Function TimeCurrentDb()
Dim db As DAO.Database
Dim tmr As clsTimer
Dim lng As Long
     Set tmr = New clsTimer
     tmr.StartTimer
     For lng = 1 To clngMaxLoop
         Set db = CurrentDb
     Next lng
     Debug.Print "CurrentDb:" & tmr.EndTimer
End Function
Function TimeDBEngine()
Dim db As DAO.Database
Dim tmr As clsTimer
Dim lng As Long
     Set tmr = New clsTimer
     tmr.StartTimer
     For lng = 1 To clngMaxLoop
         Set db = DBEngine(0)(0)
     Next lng
     Debug.Print "DBEngine(0)(0):" & tmr.EndTimer
End Function

Class timer is:

Option Compare Database
Option Explicit

Private Declare Function apiGetTime Lib "winmm.dll" _
                                     Alias "timeGetTime" () As Long
Dim lngStartTime As Long
Private Sub Class_Initialize()
     StartTimer
End Sub
Function EndTimer()
     EndTimer = apiGetTime() - lngStartTime
End Function
Sub StartTimer()
     lngStartTime = apiGetTime()
End Sub

My results were:

TimeCurrentDb
CurrentDb:859
CurrentDb:878
CurrentDb:858
CurrentDb:886
CurrentDb:892

TimeDBEngine
DBEngine(0)(0):1

I guess the lesson to take away from all this is that Currentdb is a pretty specialized construct 
that in general is not what we want to use for most things.  DBEngine(0)(0) is a pointer to the 
ACTUAL database, live, current at all times.  CurrentDB is exactly that, a pointer to the database 
at the CURRENT instant, not live, not current at all times.  And expensive to get a pointer to to boot.

John W. Colby
www.ColbyConsulting.com


Max Wanadoo wrote:
> Very interesting AD.
> 
> Any two instances of CurrentDb retrieved are not automatically in synch or
> transparent to each other. The situation could be considered akin to two
> different recordsets for the same data source.
> <<
> 
> This is exactly what I would want and expect in most cases.  I would not
> want a second instance to have any bearing whatsoever on the first and I
> would want/expect to close the second down without any effect on the first.
> 
> Good summary - many thanks
> 
> Max