[AccessD] OpenRecordSet question

Turns out it is all about the way MS implement CurrentDB.  It is not persistent.
Every time it is called it creates a clone of the current DBEngine with  a complete refresh of 
it's collections. As soon as the direct reference to it expires, it *automatically* drops out of 
scope.

That's why you can use it in  "For Each..." loop or a  "With...." block - it stays in scope until 
the end of the loop/block.  

But in this case, it is automatically dropped straight after the Set line so tdf no longer has a 
parent.  You can only persist it by assigning it to an object variable.


-- 
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