[AccessD] OpenRecordSet question

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



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: 08 August 2009 10:17
To: Access Developers discussion and problem solving
Subject: Re: [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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com