DAO TableDef error

[Dale Fye]

I must be having a brain cramp. The following code is generating a run-time
error 3420: Object invalid or no longer set.

The line that is marked with the asterisk is the one generating the error.
When I add error handling, it still errors when I try to print the tdf.name
to the debug window, not when on the Set tdf = ... line.

Public Sub test()

Dim tdf As DAO.TableDef

Set tdf = CurrentDb.TableDefs("tbl_Groups")
Debug.Print tdf.Name
Debug.Print tdf.Fields.Count
Set tdf = Nothing

End Sub

Dale

 

[Dirk Goldgar]

I must be having a brain cramp. The following code is generating a
run-time error 3420: Object invalid or no longer set.

The line that is marked with the asterisk is the one generating the error.
When I add error handling, it still errors when I try to print the
tdf.name to the debug window, not when on the Set tdf = ... line.

Public Sub test()

Dim tdf As DAO.TableDef

Set tdf = CurrentDb.TableDefs("tbl_Groups")
Debug.Print tdf.Name
Debug.Print tdf.Fields.Count
Set tdf = Nothing

End Sub

Unlike Recordsets, TableDef objects don't maintain an internal reference to
their parents, so you need to maintain the existence of the Database object
on which the TableDef is based. In your existing code, a Database object is
created and a TableDef object is created based on it, all in the one line,

Set tdf = CurrentDb.TableDefs("tbl_Groups")

But after that line is finished, the Database object is destroyed, since no
other object refers to it, and hence the parent of the TableDef object no
longer exists.

To rectify the situation, use a Database object variable:

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

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

Debug.Print tdf.Name
Debug.Print tdf.Fields.Count

Set tdf = Nothing
Set db = Nothing

 

[Dale Fye]

Thanks, Dirk.

I knew it had to be something simple.

Dale

 

[Tony Toews [MVP]]

I must be having a brain cramp. The following code is generating a run-time
error 3420: Object invalid or no longer set.

I do the same thing all the time so I know the cause. <smile> You
need to keep the database reference open while using the tdf. The
recurrence to currentdb is valid only for that line of code.

The line that is marked with the asterisk is the one generating the error.
When I add error handling, it still errors when I try to print the tdf.name
to the debug window, not when on the Set tdf = ... line.

Public Sub test()

Dim tdf As DAO.TableDef

Dim db as DAO.database

Set tdf = db.TableDefs("tbl_Groups")

Debug.Print tdf.Name
Debug.Print tdf.Fields.Count
Set tdf = Nothing

db.close; set db=nothing

Or something similar.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/