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.

Indignant

Do not use DAO! Use ADO instead. What version of MS Access? – Hayman

@MaciejLos re: "Do not use DAO! Use ADO instead." - Wrong. Absolutely wrong. – Remanence

@GordThompson, is there any specific reason for using DAO? See: utteraccess.com/wiki/index.php/Choosing_between_DAO_and_ADO – Hayman

@MaciejLos DAO continues to be the preferred method for interacting with Access database objects from VBA projects within Access itself. That's why a new Access VBA project will already have a DAO Reference defined. (Years ago Microsoft tried to force Access developers to adopt ADO but that effort failed and Microsoft went back to offering DAO as the default reference.) – Remanence

For one, you need to add a specific reference library for ADO that may not be available to all users; Microsoft ADO for DDL and Security – Everything

Thank you guys for your opinion, but i have to disagree. Of course, DAO is still used within MS Access databases, but ADO i smore powerfull in my opnion. Some functionalities of DAO are still usefull, but DAO is really old. I think it might be tons arguments for using ADO and DAO. Maybe next time? – Hayman

@MaciejLos re: "DAO is really old." - ACE DAO has been updated to handle new Access features more completely than either OLEDB or ODBC. For example, the only reliable way to insert and extract documents from an Attachment field in an Access database is via an ACE DAO Field2 object. – Remanence

Also, from the page MaciejLos linked, "Though ADO was meant to replace DAO, Microsoft has since reversed the position and made DAO the blessed data access library for Access. Furthermore, Microsoft has blessed ADO.NET, which is entirely different data access library with little in common to ADO other than the three letters. Thus, it is unlikely that ADO will receive any active development." So anyone is free to reject DAO on the grounds it "is really old", but Microsoft hasn't rejected it. – Cohort

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.)}

Remanence

What if query with the same name already exists in database? You should delete existiting query, before you create it again. – Hayman

@MaciejLos Temporary QueryDef objects (whose name is an empty string) are not saved in the QueryDefs collection. – Remanence

@Now, the answer is complete ;) By The Way: i knew that ;) – Hayman

For my select query, setting .Name ="" throws "Invalid connection string in pass-through query". I have put the code in With...End – Settler

@Settler Please ask a new question including the relevant details for your issue, including the code you are trying to use. – Remanence

Answer 2

With DAO, you could also do it like this:

Dim sqltext As String
qdfNewHospital As DAO.QueryDef

'  build the create querydef sql  string
sqltext = "INSERT INTO tblHospital (HospitalName)" & _
        " VALUES ('" & hName & "')" 

'  now create a reusable stored query def
On Error Resume Next  

With CurrentDb               
    'Delete the query if it exists
    .QueryDefs.Delete ("My_Query")
    'Now set up the querydef                                
    Set qdfNewHospital = .CreateQueryDef ("My_Query", sqltext)
    .Close
End With

Everything

Answer 3

To put it succinctly, I had the same problem.

Dim db as DAO.Database
Dim q as DAO.queryDef
...
' This failed
Set q = db.CreateQueryDef(, strSQl)
q.Execute dbFailOnError

'This worked (queryDef needs a name even if its blank)
Set q = db.CreateQueryDef("", strSQl)
q.Execute dbFailOnError

Cuvette