Geek Answers Handbook

Access VBA QueryDef.Execute () Error 3420

A very simple piece of code here causes a very nasty problem. This is a section of a larger routine, but only relevant information needs to 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 error 3420 "Object is invalid or is no longer installed" on this line:

qdfNewHospital.Execute dbFailOnError

This seems to indicate a common problem, I know where the QueryDef is created like this:

CurrentDb.CreateQueryDef

Removing prematurely due to how CurrentDb works internally. A common solution to this problem is, obviously, what I did here is to save the “snapshot” of CurrentDb in a variable and create a QueryDef from there to ensure that it will not be deleted. Additional information:

  • I checked that there are no conflicts with names elsewhere in the code
  • Nowhere in this module is CurrentDb even mentioned

Stackoverflow , , , , , , " CurrentDb , ". , . .

+4
2

, QueryDef .Name. , QueryDef, .Name CreateQueryDef:

Set qdfNewHospital = db.CreateQueryDef("")

, , , ...

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

... .

QueryDef .Name QueryDefs, "" .

( @MaciejLos .)

+5

DAO :

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
+1

More articles:


All Articles