CurrentDb.Execute delete query not deleting anything when its executed from VBA

Question

I am trying to get my code to delete a newly created record if the user cancels. for some reason Access is not deleting the record even though the query is definitely filtering for unique IDs which exist within the table. Access is not throwing any errors.

PG_ID is the unique identifier in both tables, it is a Long Integer.

I've included a sample portion of my code below. Please help!

    Dim var_PGID As String
    Dim Delete_PG_Data, Delete_PG_Upld As String
    Dim db As Database
        Set db = CurrentDb
        var_PGID = TempVars![var_PG_ID_NEW]
        Delete_PG_Data = "DELETE * " & _
                         "FROM tbl_CapEx_Projects_Group " & _
                         "WHERE PG_ID=" & var_PGID
        Delete_PG_Upld = "DELETE * " & _
                         "FROM tbl_CapEx_Projects_Group_Attachements " & _
                         "WHERE PG_ID=" & var_PGID
        Debug.Print Delete_PG_Data
        Debug.Print Delete_PG_Upld
        db.Execute Delete_PG_Data, dbFailOnError
        db.Execute Delete_PG_Upld, dbFailOnError

As requested I've switched msgbox to Debug.Print. Below is the debug.print output which runs correctly when placed in an access query.

It was a timing issue. I fixed it by committing the transaction then running the delete query. Thank you all for your input!

Private Sub cmd_Cancel_Click()
On Error Resume Next
DoCmd.SetWarnings False
If TempVars![var_NewRecord] = True Then
    Do While Not Me.Recordset.EOF
            Me.Recordset.Update
            Me.Recordset.MoveNext
        Loop
            DBEngine.CommitTrans
            Me.Recordset.Close
    Dim var_PGID As String
    Dim Delete_PG_Data, Delete_PG_Upld As String
    Dim db As Database
        Set db = CurrentDb
        var_PGID = TempVars![var_PG_ID_NEW]
        Delete_PG_Data = "DELETE * " & _
                         "FROM tbl_CapEx_Projects_Group " & _
                         "WHERE PG_ID=" & var_PGID
        Delete_PG_Upld = "DELETE * " & _
                         "FROM tbl_CapEx_Projects_Group_Attachements " & _
                         "WHERE PG_ID=" & var_PGID
        Debug.Print Delete_PG_Data
        Debug.Print Delete_PG_Upld
        db.Execute Delete_PG_Data, dbFailOnError
        db.Execute Delete_PG_Upld, dbFailOnError

    ''Me.Recordset.Delete
    ''DBEngine.BeginTrans
    ''DBEngine.CommitTrans
Else
    If Me.Saved Then
        DBEngine.Rollback
    Else
        If Me.Dirtied Then DBEngine.Rollback
    End If
End If
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
    Form_frm_CapEx_Edit_Project_Groups_Cont.Requery
DoCmd.SetWarnings True
End Sub

Hmm, looks like it should work if PG_ID is indeed a long int. Please replace MsgBox by Debug.Print and add the output from the Immediate window to your question (Ctrl+G takes you there). -- See also: How to debug dynamic SQL in VBA — Andre
– Andre, Commented Mar 29, 2016 at 14:50
Access may throw an error, but you don't see it because you didn't add dbFailOnError parameter to db.Execute. Try to copy the value of Delete_PG_Data and Delete_PG_Upld variables to query builder and execute there. — Sergey S.
– Sergey S., Commented Mar 29, 2016 at 14:50
Are you sure that TempVars![var_PG_ID_NEW] variable contains right value? — Sergey S.
– Sergey S., Commented Mar 29, 2016 at 14:52
Yes the variable TempVars![var_PG_ID_NEW] for the unique identifier is definitely correct. — Moehling
– Moehling, Commented Mar 29, 2016 at 14:55
No offense, but I think this code is going to blow up on you rather sooner than later. Running all this stuff after On Error Resume Next is dangerous, it's masking all sorts of errors - you are updating records you didn't edit, committing or rolling back transactions you didn't start, etc. -- I have the feeling that a simple Me.Dirty = False to save the new & edited record would solve your issues. — Andre
– Andre, Commented Mar 29, 2016 at 16:00

Gustav · Accepted Answer · 2016-03-29 14:59:13Z

1

.. delete a newly created record if the user cancels

Sounds like the record doesn't get saved. Even if not, it could be a timing issue as the run the query in another context than the form.

If it really has been created, the simplest and fastest method is to delete the record from the RecordsetClone of the form.

answered Mar 29, 2016 at 14:59

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Moehling Over a year ago

It may be a timing issue. I will attach the code which creates the new record above. the issue is I have to create another record at the same time as the main table. the second table holds all of the attachments since they were slowing down my queries when on the same table. the second record is where I'm having trouble, as it is not part of the initialy created main record, but joined.

marlan Over a year ago

It looks like a timing issue to me too. The lock from Insertion wasn't removed yet, deletion can't take place. Lock takes place in File System, and could take time to remove. if RecordsetClone doesn't work, Form's Recordset, witch may be the object applying the lock.

Moehling Over a year ago

Thank you Gustav, it was a timing issue. I fixed it by committing the transaction then deleting, not sure why the second attachment table wouldn't delete before, but in any case this seemed to fix it. I've attached my modified code above. Thanks again

Gustav Over a year ago

OK. I didn't notice the transaction handling. When using this, do remember to stick with the DbEngine(0)(0) database object, don't use CurrentDb as it knows nothing about an active transaction.

Sergey S. Over a year ago

@Gustav, As I know, transactions controlled by current workspace Dbengine(0), not by database Dbengine(0)(0) or a copy of it - CurrentDb, right?

|

Adarsh Madrecha · Accepted Answer · 2016-03-29 14:52:15Z

0

Use DoCmd.RunSQL

example :

Public Sub DoSQL() 

    Dim SQL As String 

    SQL = "UPDATE Employees " & _ 
          "SET Employees.Title = 'Regional Sales Manager' " & _ 
          "WHERE Employees.Title = 'Sales Manager'" 

    DoCmd.RunSQL SQL 

End Sub

Hence your new code will be as below:

Dim var_PGID As String
Dim Delete_PG_Data, Delete_PG_Upld As String
var_PGID = TempVars![var_PG_ID_NEW]
Delete_PG_Data = "DELETE * " & _
                     "FROM tbl_CapEx_Projects_Group " & _
                     "WHERE PG_ID=" & var_PGID
Delete_PG_Upld = "DELETE * " & _
                     "FROM tbl_CapEx_Projects_Group_Attachements " & _
                     "WHERE PG_ID=" & var_PGID
MsgBox Delete_PG_Data
MsgBox Delete_PG_Upld
DoCmd.RunSQL Delete_PG_Data
DoCmd.RunSQL Delete_PG_Upld

answered Mar 29, 2016 at 14:52

Adarsh Madrecha

8,20514 gold badges78 silver badges129 bronze badges

3 Comments

Andre Over a year ago

Actually db.Execute is a better choice than DoCmd.RunSQL : fmsinc.com/microsoftaccess/query/action-queries/…

Sergey S. Over a year ago

DoCmd.RunSQL requires also disabling default warnings before execution . If procedure fails before enabling, you won't be prompted for any actions in design mode, it may cause accidental damage to database

Adarsh Madrecha Over a year ago

Thanks Andre and @Sergey for share this info. I will also start using db.execute in my future projects.

Collectives™ on Stack Overflow

CurrentDb.Execute delete query not deleting anything when its executed from VBA

2 Answers 2

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related