prob with tabledef in Access2002

[James]

In Access2002 I declare a tabledef variable and use it as
follows:

Dim tdf As dao.TableDef
Set tdf = CurrentDB.TableDefs("tbl1") '--tbl1 exists
Debug.Print tdf.Name


This code bombs out at Debug.Print tdf.Name

The error message say "Object invalid or no longer set"
Could someone explain why I am getting this error? Is
there a workaround?

Thanks,
James

 

[Dirk Goldgar]

In Access2002 I declare a tabledef variable and use it as
follows:

Dim tdf As dao.TableDef
Set tdf = CurrentDB.TableDefs("tbl1") '--tbl1 exists
Debug.Print tdf.Name


This code bombs out at Debug.Print tdf.Name

The error message say "Object invalid or no longer set"
Could someone explain why I am getting this error? Is
there a workaround?

It happens because the TableDef object is destroyed when its parent
object (the Database object returned by the CurrentDb() function) is
destroyed, and your code lets that happen as soon as the Set statement
is complete. Do it this way:

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb
Set tdf = db.TableDefs("tbl1")

Debug.Print tdf.Name

' ... do stuff with tdf ...

Set tdf = Nothing
Set db = Nothing

 

[James]

Thanks very much. That was an excellent explanation. I
just upgraded to Acc2002 from 97. I guess I just have to
get used to a few new things.

 

[Dirk Goldgar]

Thanks very much. That was an excellent explanation. I
just upgraded to Acc2002 from 97. I guess I just have to
get used to a few new things.

You're welcome, and I'm glad you found the explanation helpful. I don't
think this business with the TableDef object has changed from Access 97
to 2002, though. People are often misled by the fact that Recordset
objects don't behave the same way, but that's because the Recordset
object maintains a reference to its parent object, thus keeping that
object "alive".