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](http://stackoverflow.com/a/1099570/3820271) — Andre, Mar 29 '16 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., Mar 29 '16 at 14:50
Are you sure that TempVars![var_PG_ID_NEW] variable contains right value? — Sergey S., Mar 29 '16 at 14:52
Yes the variable TempVars![var_PG_ID_NEW] for the unique identifier is definitely correct. — Moehling, Mar 29 '16 at 14:55
HansUp, access is only showing one matching record, which makes sense as the where statement is filtering to the unique table identifier. — Moehling, Mar 29 '16 at 15:04
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, Mar 29 '16 at 16:00

score 1 · Answer 1 · answered Mar 29 '16 at 14:59

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 '16 at 14:59

Gustav

53,498
7
29
55

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. – Moehling Mar 29 '16 at 15:10
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. – marlan Mar 29 '16 at 15:13
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 – Moehling Mar 29 '16 at 15:35
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. – Gustav Mar 29 '16 at 16:20
@Gustav, As I know, transactions controlled by current workspace Dbengine(0), not by database Dbengine(0)(0) or a copy of it - CurrentDb, right? – Sergey S. Mar 29 '16 at 16:44
Yes, and by using DbEngine(0)(0) you are sure to use a database object controlled by the transaction. – Gustav Mar 30 '16 at 07:25

score 0 · Answer 2 · answered Mar 29 '16 at 14:52

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 '16 at 14:52

Adarsh Madrecha

6,364
11
69
117

2

Actually `db.Execute` is a better choice than `DoCmd.RunSQL` : http://www.fmsinc.com/microsoftaccess/query/action-queries/SuppressWarningMessages.htm – Andre Mar 29 '16 at 14:55
1

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 – Sergey S. Mar 29 '16 at 14:56
Thanks Andre and @Sergey for share this info. I will also start using db.execute in my future projects. – Adarsh Madrecha Mar 29 '16 at 15:00

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

2 Answers2