[AccessD] OpenRecordSet question

Use of CurrentDb and DbEngine(0)(0)
(Opening of recordsets, tabledefs, querydefs)
==============================

    It is observed that tabledef object created directly via CurrentDb as per following sample statement, is not persistent. Any attempt at its use in subsequent statements, or passing as an argument to external functions, attracts error 3420 (Object invalid or no longer set):

    Set tdf = CurrentDb.TableDefs("MyTable")    ' (A)

    However, if an object variable (say db) is first set to CurrentDb and that variable (db) is used in lieu of CurentDb in the above statement, there is no error.

    Interestingly, directly opened tabledef object can be passed as an argument to external function in following style, without attracting any error:

    Debug.Print MyFunction(CurrentDb.TableDefs("MyTable"))    ' (B)

    However, any attempt to use such a style in a With / End With block as shown below, will attract error 3420:

'============================        ' (C)
    With CurrentDb.TableDefs("MyTable")
        Debug.Print .Fields.Count        ' Error 3420
        Debug.Print .Fields(0).Name    ' Error 3420
    End With
'============================

    It is noticed that recordset and querydef objects do not suffer from above limitations. These are persistent whether created directly via CurrentDb as per (A) above or via db where db is an object pointer to CurrentDb. Of course these can also be passed directly as an argument to external function in a manner similar to statement (B) above. Moreover, there is no error on adoption of With / End With construct similar to (C) above.

    Even with tabledef object, if DbEngine(0)(0) is used in lieu of CurrentDb, it becomes persistent and all problems outlined earlier disappear. It can be used in all styles, including With / End With as per (C) above, without attracting any error.

    The reason for sturdier performance with DbEngine(0)(0) lies in the fact that it is native to Jet, while CurrentDb, is an extra layer (on the fly) provided by Access on top of Jet. Each call to CurrentDb builds a new instance of database object. Each such new instance is ephemeral and disappears unless a pointer is set to it. 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.

    As a corollary, if there are going to be multiple calls to database object and the situation does not permit keeping it alive via a standing global variable, adoption of DbEngine(0)(0) in lieu of CurrentDb could be preferred for improved performance.

    Note - The overhead involved in multiple creation of new instances via CurrentDb is likely to become heavier with greater number of objects in the database.
    
Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Stuart McLachlan 
  To: Access Developers discussion and problem solving 
  Sent: Saturday, August 08, 2009 10:32
  Subject: Re: [AccessD] OpenRecordSet question


  Yep, that errors for me as well, but this doesn't:

  Function test()
  Dim tdf As TableDef
  For Each tdf In CurrentDb.TableDefs
    MsgBox tdf.Name
  Next
  End Function

  I'll have to look into this some more.
  -- 
  Stuart


  On 7 Aug 2009 at 21:17, Doug Steele wrote:

  > Interesting...this is in my standard relinking code as well.
  > 
  > I just double checked to make sure I wasn't making a mistake before I posted
  > the message.  Access 2003, and the following code:
  > 
  > dim tdf as TableDef
  > Set tdf = Currentdb.TableDefs(nextTable) 'where nextTable contains a valid
  > table name
  > msgbox tdf.name
  > 
  > the msgbox statement errors on 'object invalid or no longer set'.  If I
  > don't use Currentdb but a dimmed db variable, it works correctly.
  > 
  > Am I using the 'set' incorrectly?  But if I leave that out, I get a compiler
  > message 'Invalid use of property'.
  > 
  > Doug Steele
  > 
  > 
  > On Fri, Aug 7, 2009 at 5:21 PM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote:
  > 
  > > Tha's strange,  I use this sort of relinking code in just about every
  > > system I write and it's
  > > never caused a problem.
  > >
  > >
  > > Dim tbl as TableDef
  > > ...
  > >   For Each tbl In CurrentDb.TableDefs
  > >         If Left$(tbl.Name, 3) = "tbl" Then renewlink _
  > >              tbl.Name, CurrentProject.Path & strBEName"
  > >   Next
  > > ...
  > > Function renewlink(tablename As String, datafile As String) As Long
  > > DoCmd.DeleteObject acTable, tablename
  > > DoCmd.TransferDatabase acLink, "Microsoft Access", datafile, _
  > > acTable, tablename, tablename, False
  > > End Function
  > > --
  > > Stuart
  > >
  > > On 7 Aug 2009 at 15:59, Doug Steele wrote:
  > >
  > > > I discovered this when I found that
  > > >
  > > > Set tdf = Currentdb.Tabledefs("myTableDef")
  > > >
  > > >  doesn't work, but
  > > >
  > > > dim myDB as Database
  > > > set tdf = myDB.Tabledefs("myTableDef")
  > > >
  > > > does work