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
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
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.
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.
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.
Dim td As TableDef
Set td = CurrentDb.TableDefs("Customers")
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!
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