title: Access VBA QueryDef.Execute() Error 3420
body: <p>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.</p>
  
  <pre><code>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)" &amp; _
          " VALUES ('" &amp; hName &amp; "')"
      qdfNewHospital.Execute dbFailOnError
      Dim iAffected As Integer
      iAffected = qdfNewHospital.RecordsAffected
      Debug.Print "Inserting hospital " &amp; hName &amp; " affected " &amp; iAffected &amp; " row(s)"
  End If
  </code></pre>
  
  <p>I get an error 3420 "Object invalid or no longer set" on this line:</p>
  
  <pre><code>qdfNewHospital.Execute dbFailOnError
  </code></pre>
  
  <p>This would seem to indicate a common problem I'm aware of where a QueryDef created like so:</p>
  
  <pre><code>CurrentDb.CreateQueryDef
  </code></pre>
  
  <p>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:</p>
  
  <ul>
  <li>I've verified there are no naming conflicts elsewhere in the code</li>
  <li>Nowhere else in this module is CurrentDb even referenced</li>
  </ul>
  
  <p>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.</p>