Access VBA QueryDef.Execute() Error 3420

Question

A very simple snippet of code here is posing a very annoying problem. This is a section of a larger subroutine but the only relevant information should be found here.

Dim db As DAO.Database
Set db = CurrentDb

' If the associated hospital is new, add it first
If newHName = True Then
    Dim qdfNewHospital As DAO.QueryDef
    Set qdfNewHospital = db.CreateQueryDef
    qdfNewHospital.SQL = "INSERT INTO tblHospital (HospitalName)" & _
        " VALUES ('" & hName & "')"
    qdfNewHospital.Execute dbFailOnError
    Dim iAffected As Integer
    iAffected = qdfNewHospital.RecordsAffected
    Debug.Print "Inserting hospital " & hName & " affected " & iAffected & " row(s)"
End If

I get an error 3420 "Object invalid or no longer set" on this line:

qdfNewHospital.Execute dbFailOnError

This would seem to indicate a common problem I'm aware of where a QueryDef created like so:

CurrentDb.CreateQueryDef

Is prematurely disposed of due to how CurrentDb works internally. The common solution to this problem is obviously what I've done here, save the "CurrentDb snapshot" in a variable and create the QueryDef from there to assure it's not being disposed of. Some additional details:

• I've verified there are no naming conflicts elsewhere in the code
• Nowhere else in this module is CurrentDb even referenced

I've scoured Stackoverflow and various help forums trying to find a solution to this seemingly straightforward issue, and the solution in any situation analogous to this is invariably "don't call CurrentDb directly, store it in a variable instead". Yet I've done that and the problem persists. Any insight into this would be greatly appreciated.

Answer 1

You are getting that error because the QueryDef object you create does not have a .Name property. Normally when we create a temporary QueryDef object we supply the .Name property as an empty string argument to the CreateQueryDef method:

Set qdfNewHospital = db.CreateQueryDef("")

Or, I suppose you could do it in two steps if you prefer ...

Set qdfNewHospital = db.CreateQueryDef
qdfNewHospital.Name = ""

... but the first way is much more common.

When you create a temporary QueryDef object by setting its .Name to an empty string the object is not saved in the QueryDefs collection so you don't need to worry about "cleaning up" after using it.

_{(Thanks to @MaciejLos for suggesting an improvement to this answer.)}