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

KnowledgeBase Mausoleum

Looking for that long-lost KnowledgeBase article from 2003 but only finding a 404 error? This website brings dead KB articles back to life.

No Longer SetMike Wolfe

External references

Application.CurrentDb method (Access)

Office VBA reference topic

Microsoft Docso365devx

ACC: “Object Invalid or No Longer Set” Error Using CurrentDb

Microsoft

Object Invalid Or No Longer Set - Error

I have a few people getting this error in my database. Happens when they try to save a record and then get a new one. I have searched on Google and not found anything that seems to be related to my problem. I’m just curious how to get around this error. I don’t mind if it pops up, but I want...

Access World Forumsodin1701

Image by Andrew Martin from Pixabay