How to fix "Run-time error '3012': Object 'MyQuery' already exists"

Question

How to fix "Run-time error '3012': Object 'MyQuery' already exists"

calc_pro 20

Hi everyone, I was trying to update some information on the database using Form and suddenly I got an error message saying, "Run-time error '3012': Object 'MyQuery' already exists". It was working perfectly fine before till now. I have a little knowledge in Microsoft Access, and I will appreciate if anyone can help me with this issue. Thank you very much in advance.

Answer 1

Ken Sheridan 2,496

In situations like this I would recommend that the querydef object be deleted before calling the CreateQueryDef method, handling and ignoring the error if the query does not exist:

     Const QUERYDOESNOTEXIST = 3265

     On Error Resume Next
     CurrentDb.QueryDefs.Delete "MyQuery"
     Select Case Err.Number
         Case 0
         ' no error
         Case QUERYDOESNOTEXIST 
         ' anticipated error, ignore
         Case Else
         MsgBox "An unexpected error has occurred. Please contact the database administrator."
    End Select

    DoCmd.Close acForm, "folderForm", acSaveNo

calc_pro 20

Thank you very much Ken. I'm not getting the error message anymore, however, When I press save button, it's not updating the information. Attached please find the entire code. I really appreciate your help.

Option Compare Database

Private Sub cmdDelete_Click()
    Dim db As DAO.Database
    Dim stmt As String
    
    Set db = CurrentDb()
    
    stmt = "DELETE FROM folder WHERE folderYear = " & folderYear.Value & " AND folderNum = '" & folderNum.Value & "';"
    
    db.Execute stmt, dbFailOnError
    DoCmd.Close acForm, "folderForm", acSaveNo
    
End Sub

Private Sub cmdSave_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim stmt As String
    Dim temp As DAO.QueryDef
    
    Set db = CurrentDb()
    
    If ecsNum.Value = "" Or IsNull(ecsNum) Then
        MsgBox "You must enter an ECS number."
        Exit Sub
    Else
        stmt = "SELECT * FROM folder WHERE ecsNum = '" & ecsNum.Value & "' AND (folderYear <> " & folderYear.Value & " OR folderNum <> '" & folderNum.Value & "');"
        Set rs = CurrentDb.OpenRecordset(stmt)
        If Not rs.EOF Then
            MsgBox "An existing ECS number already exists in the system."
            Exit Sub
        End If
    End If
    
    If recDate.Value <> "" And Not IsNull(recDate) Then
        recDateV = "'" & recDate.Value & "'"
    Else
        recDateV = "null"
    End If
    
    If lawDate.Value <> "" And Not IsNull(lawDate) Then
        lawDateV = "'" & lawDate.Value & "'"
    Else
        lawDateV = "null"
    End If
    
    If settleDate.Value <> "" And Not IsNull(settleDate) Then
        settleDateV = "'" & settleDate.Value & "'"
    Else
        settleDateV = "null"
    End If
    
    If occDate.Value <> "" And Not IsNull(occDate) Then
        occDateV = "'" & occDate.Value & "'"
    Else
        occDateV = "null"
    End If
    
    If estDate.Value <> "" And Not IsNull(estDate) Then
        estDateV = "'" & estDate.Value & "'"
    Else
        estDateV = "null"
    End If
    
    If gmDate.Value <> "" And Not IsNull(gmDate) Then
        gmDateV = "'" & gmDate.Value & "'"
    Else
        gmDateV = "null"
    End If
    
    If reportRec.Value <> "" And Not IsNull(reportRec) Then
        reportRecV = "'" & reportRec.Value & "'"
    Else
        reportRecV = "null"
    End If
    
    'department status
    If distributionServices.Value <> "" And Not IsNull(distributionServices) Then
        distributionServicesV = "'" & distributionServices.Value & "'"
        If checkDistributionServices.Value = 0 Then
            MsgBox "Check Distribution Services"
            Exit Sub
        End If
    Else
        distributionServicesV = "null"
    End If
    If subsurfaceConstruction.Value <> "" And Not IsNull(subsurfaceConstruction) Then
        subsurfaceConstructionV = "'" & subsurfaceConstruction.Value & "'"
        If checkSubsurfaceConstruction.Value = 0 Then
            MsgBox "Check Sub-Surface Construction"
            Exit Sub
        End If
    Else
        subsurfaceConstructionV = "null"
    End If
    If installationApparatus.Value <> "" And Not IsNull(installationApparatus) Then
        installationApparatusV = "'" & installationApparatus.Value & "'"
        If checkInstallationApparatus.Value = 0 Then
            MsgBox "Check Installation & Apparatus"
            Exit Sub
        End If
    Else
        installationApparatusV = "null"
    End If
    If underground.Value <> "" And Not IsNull(underground) Then
        undergroundV = "'" & underground.Value & "'"
        If checkUnderground.Value = 0 Then
            MsgBox "Check Underground"
            Exit Sub
        End If
    Else
        undergroundV = "null"
    End If
    If cable.Value <> "" And Not IsNull(cable) Then
        cableV = "'" & cable.Value & "'"
        If checkCable.Value = 0 Then
            MsgBox "Check Cable"
            Exit Sub
        End If
    Else
        cableV = "null"
    End If
    If overhead.Value <> "" And Not IsNull(overhead) Then
        overheadV = "'" & overhead.Value & "'"
        If checkOverhead.Value = 0 Then
            MsgBox "Check Overhead"
            Exit Sub
        End If
    Else
        overheadV = "null"
    End If
    If fieldOperations.Value <> "" And Not IsNull(fieldOperations) Then
        fieldOperationsV = "'" & fieldOperations.Value & "'"
        If checkFieldOperations.Value = 0 Then
            MsgBox "Check Field Operations"
            Exit Sub
        End If
    Else
        fieldOperationsV = "null"
    End If
    If networks.Value <> "" And Not IsNull(networks) Then
        networksV = "'" & networks.Value & "'"
        If checkNetworks.Value = 0 Then
            MsgBox "Check Networks"
            Exit Sub
        End If
    Else
        networksV = "null"
    End If
    

    Dim TestString As String
TestString = ""
    If checkDistributionServices.Value <> 0 And IsNull(distributionServices.Value) Then
       TestString = TestString & "Distribution Services/"
    End If
    
    If checkSubsurfaceConstruction.Value <> 0 And IsNull(subsurfaceConstruction.Value) Then
        TestString = TestString & "Sub-Surface Construction/"
    End If
    
    If checkInstallationApparatus.Value <> 0 And IsNull(installationApparatus.Value) Then
        TestString = TestString & "Installation & Apparatus/"
    End If
    
    If checkUnderground.Value <> 0 And IsNull(underground.Value) Then
        TestString = TestString & "Underground/"
    End If
        
    If checkCable.Value <> 0 And IsNull(cable.Value) Then
        TestString = TestString & "Cable/"
    End If
    
    If checkOverhead.Value <> 0 And IsNull(overhead.Value) Then
        TestString = TestString & "Overhead/"
    End If
    
    If checkFieldOperations.Value <> 0 And IsNull(fieldOperations.Value) Then
        TestString = TestString & "FieldOperations/"
    End If
    If checkNetworks.Value <> 0 And IsNull(networks.Value) Then
        TestString = TestString & "Networks/"
    End If
    
    If Not IsNull(estDate.Value) Then
        TestString = TestString & "Enginnering/"
    End If
    If IsNull(estDate.Value) And IsNull(reportRec.Value) Then
        If rptgDept.Value = 1 Then
        TestString = TestString & "Construction Mgmt/"
        End If
        If rptgDept.Value = 2 Then
        TestString = TestString & "CM-Buildings and Stations/"
        End If
        If rptgDept.Value = 3 Then
        TestString = TestString & "CM-Construction Brooklyn/"
        End If
        If rptgDept.Value = 4 Then
        TestString = TestString & "CM-Construction Queens/"
        End If
        If rptgDept.Value = 5 Then
        TestString = TestString & "CM-Public Improvement BKLYN/"
        End If
        If rptgDept.Value = 6 Then
        TestString = TestString & "CM-Public Improvement QNS/"
        End If
        If rptgDept.Value = 7 Then
        TestString = TestString & "Const.Mgmt-Survey/"
        End If
        If rptgDept.Value = 8 Then
        TestString = TestString & "Const.Mgmt-Survey BKLYN/"
        End If
        If rptgDept.Value = 9 Then
        TestString = TestString & "Const.Mgmt-Survey QNS/"
        End If
        If rptgDept.Value = 10 Then
        TestString = TestString & "Const.Mgmt-Telecom/"
        End If
        If rptgDept.Value = 11 Then
        TestString = TestString & "Cable/"
        End If
        If rptgDept.Value = 12 Then
        TestString = TestString & "Energey Services/"
        End If
        If rptgDept.Value = 13 Then
        TestString = TestString & "Gas/"
        End If
        If rptgDept.Value = 14 Then
        TestString = TestString & "Installation & Apparatus/"
        End If
        If rptgDept.Value = 15 Then
        TestString = TestString & "Not Applicable/"
        End If
        If rptgDept.Value = 16 Then
        TestString = TestString & "Distribution Services/"
        End If
        If rptgDept.Value = 17 Then
        TestString = TestString & "Overhead/"
        End If
        If rptgDept.Value = 18 Then
        TestString = TestString & "Other/"
        End If
        If rptgDept.Value = 19 Then
        TestString = TestString & "Underground/"
        End If
        If rptgDept.Value = 20 Then
        TestString = TestString & "Engineering/"
        End If
    
    End If
    
    If IsNull(estDate.Value) And Not IsNull(reportRec.Value) Then
    TestString = TestString & remarks.Value & "/"
    End If
    
    If Len(TestString) <> 0 Then
        TestString = Left(TestString, Len(TestString) - 1)
        Departments.Value = TestString
    Else
        Departments.Value = ""
    End If
    

    
    If isEdit = False Then
        stmt = "INSERT INTO folder (folderYear, folderNum, boro, recDate, electricConstruction, ecsNum, lawDate, settleDate, settleAmt, location, remarks, occDate, estDate, gmDate, reportRec, billAmt, dmgTypeID, rptgDeptID, distributionServices, subsurfaceConstruction, installationApparatus, underground, cable, overhead, fieldOperations, networks, distributionServicesEnable, subsurfaceConstructionEnable, installationApparatusEnable, undergroundEnable, cableEnable, overheadEnable, fieldOperationsEnable, networksEnable)" & _
            "VALUES(" & folderYear.Value & ", '" & folderNum.Value & "', '" & boro.Value & "', " & recDateV & ", '" & Departments.Value & "', '" & ecsNum.Value & "', " & lawDateV & "," & settleDateV & "," & _
            Nz(settleAmt.Value, 0) & ",'" & location.Value & "','" & remarks.Value & "'," & occDateV & "," & estDateV & "," & gmDateV & "," & _
            reportRecV & "," & Nz(billAmt.Value, 0) & "," & dmgType.Value & "," & rptgDept.Value & ", " & distributionServicesV & "," & _
            subsurfaceConstructionV & ", " & installationApparatusV & "," & undergroundV & ", " & cableV & ", " & overheadV & ", " & fieldOperationsV & ", " & networksV & "," & _
            checkDistributionServices.Value & ", " & checkSubsurfaceConstruction.Value & ", " & checkInstallationApparatus.Value & "," & checkUnderground.Value & ", " & checkCable.Value & ", " & checkOverhead.Value & ", " & checkFieldOperations.Value & ", " & checkNetworks.Value & ");"
    Else
        stmt = "UPDATE MyQuery SET recDate = " & recDateV & ", electricConstruction = '" & Departments.Value & "', ecsNum = '" & ecsNum.Value & "', lawDate = " & lawDateV & ", settleDate = " & _
                settleDateV & ", settleAmt = " & Nz(settleAmt.Value, 0) & ", location = '" & location.Value & "', remarks = '" & remarks.Value & "', occDate = " & _
                occDateV & ", estDate = " & estDateV & ", gmDate = " & gmDateV & ",reportRec = " & reportRecV & ", billAmt = " & Nz(billAmt.Value, 0) & ", dmgTypeID = " & _
                dmgType.Value & ", rptgDeptID = " & rptgDept.Value & ", distributionServices = " & distributionServicesV & ", subsurfaceConstruction = " & subsurfaceConstructionV & ", distributionServicesEnable = " & _
                checkDistributionServices.Value & ", subsurfaceConstructionEnable = " & checkSubsurfaceConstruction.Value & ", installationApparatusEnable = " & checkInstallationApparatus.Value & ", undergroundEnable = " & checkUnderground.Value & ", cableEnable = " & _
                checkCable.Value & ", overheadEnable = " & checkOverhead.Value & ", fieldOperationsEnable = " & checkFieldOperations.Value & ", networksEnable = " & checkNetworks.Value & ", installationApparatus = " & _
                installationApparatusV & ", underground = " & undergroundV & ", cable = " & cableV & ", overhead = " & overheadV & ", fieldOperations = " & fieldOperationsV & ", networks = " & networksV & _
                " WHERE folderYear = " & folderYear.Value & " AND folderNum = '" & folderNum.Value & "';"
        
    End If
    
    Const QUERYDOESNOTEXIST = 3265
    
    On Error Resume Next
    CurrentDb.QueryDefs.Delete "MyQuery"
    Select Case Err.Number
        Case 0
        ' no error
        Case QUERYDOESNOTEXIST
        ' anticipated error, ignore
        Case Else
        MsgBox "An unexpected error has occurred. Please Contact the database administrator."
    End Select
    
    DoCmd.Close acForm, "folderForm", acSaveNo
    
    
End Sub


Private Sub Command71_Click()

            
   
End Sub

Private Sub installationApparatus_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub recDate_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub recDate_Exit(Cancel As Integer)

End Sub

Private Sub recDate_LostFocus()

End Sub

Private Sub recDate_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

End Sub

Answer 2

Rahul Randive 5,656 Microsoft Employee

Hi,

Thanks for your question.

Please check below blog for possible solution to an error you are getting.

https://www.errorvault.com/en/troubleshooting/runtime-errors/microsoft/microsoft-access/error-3012_microsoft-access-error-3012

If you find it unhelpful, feel free to reach out to here.

Thank you!

Answer 3

Jiachen Li-MSFT 21,871 Microsoft Vendor

Hi @calc_pro ,

It is recommend that you determine whether a new query already exists before creating a new query in your code.

Could you please provide more information about the code in which the error occurred and what you did when the error occurred.

Answer 4

Thank you very much for your prompt response both of you. The thing is that I did not create this database, but based on what I was told they were getting this same error often however it would fix automatically after restarting the PC. But this is not the case now because it's been more than 3 days, and I'm still getting the same error. This form was working fine couple of days ago, I was able to update the information until it suddenly stopped working.

After the error message I click debug button and it highlights line of code which starts with set temp. I tried compact and repair the database. I also tried to create new database and import queries, forms, tables from source database, nothing fixed the issue.

Thank you very much again.

    End If
    
    '''On Error GoTo PROC_ERR
    
    Set temp = CurrentDb.CreateQueryDef("MyQuery", "SELECT * FROM folder")
    db.Execute stmt, dbFailOnError
    CurrentDb.QueryDefs.Delete "MyQuery"
    DoCmd.Close acForm, "folderForm", acSaveNo

    
    Exit Sub
    
PROC_ERR:
  MsgBox "An unexpected error has occurred. Please contact the database administrator."
  Exit Sub

How to fix "Run-time error '3012': Object 'MyQuery' already exists"

3 additional answers

Activity