CurrentDB and the "Object invalid or no longer set" error

There are many causes of AccessError(3420), "Object invalid or no longer set."

This can be a difficult error to troubleshoot, because it is often so hard to reproduce.  

The TableDefs Collection

Here are a couple of instances of the error related to the CurrentDB function and the TableDefs collection.  For each instance, I've included a second example that avoids the error.

I used the "MSysObjects" table for my examples because every Access database includes this hidden system table.

Sub CurrentDB_Fail()
    Dim td As DAO.TableDef
    Set td = CurrentDb.TableDefs("MSysObjects")
    Debug.Print td.Name  'Object invalid or no longer set
End Sub

Sub CurrentDB_Pass()
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Dim td As DAO.TableDef
    Set td = db.TableDefs("MSysObjects")
    Debug.Print td.Name
End Sub

Sub CurrentDB_With_Fail()
    With CurrentDb.TableDefs("MSysObjects")
        Debug.Print .Name  'Object invalid or no longer set
    End With
End Sub

Sub CurrentDB_With_Pass()
    Dim db As DAO.Database
    Set db = CurrentDb
    
    With db.TableDefs("MSysObjects")
        Debug.Print .Name
    End With
End Sub

The QueryDefs Collection

The QueryDefs collection behaves differently than the TableDefs collection.  Let's copy the CurrentDB_With_Fail() function above, but replace TableDefs("MSysObjects") with QueryDefs("Query1").  You would think it would fail the same way, but curiously it works without any problem at all:

Sub CurrentDB_Form_Pass()
    With CurrentDb.QueryDefs("Query1")
        Debug.Print .Name
    End With
End Sub

The Relations Collection

The Relations collection performs more like the TableDefs collection than the QueryDefs collection.  If you want to avoid the error, you need to set a reference to a declared database object variable (e.g., Set db = CurrentDb).

Sub CurrentDB_Relations_Fail()
    With CurrentDb.Relations(0)
        Debug.Print .Name    'Object invalid or no longer set
    End With
End Sub

Sub CurrentDB_Relations_Pass()
    Dim db As DAO.Database
    Set db = CurrentDb
    
    With db.Relations(0)
        Debug.Print .Name
    End With
End Sub

What's Going On Here?

I ... don't exactly know.  

I had typed up a whole bunch of theories, but none of them quite made sense, so I deleted them lest I add to the confusion.  In lieu of those theories, I'll provide a couple of links and references to relevant articles and discussions.

Retired Microsoft KnowledgeBase Article

First, let's go into the internet Wayback Machine to find some official documentation on the subject (visit the MS KB Mausoleum to search old knowledgebase articles):

SYMPTOMS
When you refer to properties and methods belonging to objects created with the CurrentDb function, you may receive the following error message: Object invalid or no longer set.

CAUSE
When you set an object variable, such as a TableDef object, which requires a reference to a database object, your code refers directly to the CurrentDb function instead of referring to a database object variable that you set with the CurrentDb function.

RESOLUTION
Create a database object variable in your code that refers to the CurrentDb function, rather than using the CurrentDb function directly in Set statements to create other objects.

MORE INFORMATION (Steps to Reproduce Behavior)
The following example attempts to use the CurrentDb function to return a pointer to the database that is currently open in Microsoft Access. Because the code does not assign that database to an object variable, the pointer returned by the CurrentDb function is temporary and becomes invalid after the TableDef object is set. Consequently, any later references in your code to the TableDef object variable will result in an error.
Sub CurrentDbFail()
   Dim td As TableDef
   Set td = CurrentDb.TableDefs("Customers")
   MsgBox td.Name
End Sub

Former Access MVP Leigh Purvis Weighs In

Leigh referenced this phenomenon in a comment he posted on the AccessWorld forum some time ago:

FWIW the CurrentDb scope phenominon is quite quantifiable.
It's true that it doesn't always create a persisted parent object for it's DAO child collection objects, and so always using an object variable is a safe guard.

Leigh goes on to reference an extended discussion on UtterAccess, but unfortunately that link is now dead.

If you know of any other relevant discussions around this topic, please link to them in the comments below.  Thanks!

Bottom Line

To be safe, always assign the result of the CurrentDb function to a database variable before using it:

Dim db As DAO.Database
Set db = CurrentDb

Referenced articles

External references

Image by Andrew Martin from Pixabay