[AccessD] OpenRecordSet question

Further:  

CurrentDB is not an object!
It is a *method*  which returns the clone Database object.

Which would explain why it can't persist once the reference to it is over.

-- 
Stuart


On 8 Aug 2009 at 15:45, Stuart McLachlan wrote:

> 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
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com