TableDef assignation - Error 3420

[Rod]

Has anyone else experienced this? Is it a known bug or is my system screwed?

I should be able to assign a TableDef object reference using the following syntax

Set tdf = CurrentDb.TableDefs("tblItems") or
Set tdf = CurrentDb.TableDefs![tblItems]

In fact both variations appear to work but when the object variable is next used, VBA issues an error 3420, 'Object invalid or no longer set.' This occurs even if the subsequent reference to the object variable is in the next statement.

However substituting the following code works.

For each tdf in CurrentDb.TableDefs
If tdf.Name = "tblItems" Then Exit For
Next

The assignation of the object variable, tdf is now persistent - at least within the current procedure. However if I declare it as a module level variable then the assignation is 'dropped' between procedures.



I have not tried using the numerical index assignation.

[Rod]

Aha! Intelligence on the web suggests that CurrentDb is the culprit and I do remember reading that CurrentDb can be temperamental.

It is said that

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tblItems")

will work.

I shall now investigate whether the tdf assignation can be made to persist across procedures.