I need help with deleting related records

[jjz0512]

I have an 2007 Access database being used only as a UI to Oracle. The top part of my main form(parent), is for demographic data and has 5 tab forms on the bottom covering; Compensation, Site Worked, Privileges, Licenses and CME data. I have a Delete Record button on all 5 forms, so I can delete a record for an tab/sub form. To make life easier, I would like to Delete Record button I placed on the parent form, Demographics, to delete all the related information on the tab forms.

Demographics, Compensation, Privileges, Licenses, and CME tab data/tables are 1 to 1 relationship with a 1 to main on the Site Worked tab data/table. EMRI, is the primary key on all the tables. Can this operation be performed by a cascading delete?

[RuralGuy]

I take it you do not want to use the built in Cascading Delete feature?

[jjz0512]

RuralGuy, I really don't know. I'm open to anything that eliminates the using individual Delete Records buttons on all the tabs and then the parent Delete Record button.

[RuralGuy]

I don't use it because it is just too powerful for me but it may be just what you are looking for. Depending on who is using the database and how much warning you provide (Are you sure?) it is available in the Relationship screen as I recall. Access knows what is related but it is in the system tables for Access. If you enable it, test it on a copy first.

[RuralGuy]

In order for Relationships to work in MS Access they must be defined in the Front End. I'll admit, I am assuming the same is true of other linked tables such as Oracle and SQL. If that is not true then my thoughts are all wet.

[ssanfu]

In the event that cascading deletes won't work with an Oracle BE, you can write VBA code to do the deletes in whatever order you want. Get the EMRI value from the main form, then use the EMRI to delete from the related tables, then delete from the main table.

[RuralGuy]

Thanks Steve. Uhh...what is the EMRI?

[jjz0512]

I think that needs to be set in the Oracle database first, and then you need to set it up in the Access front end. I think what I really need to do is to delete all the records in the child forms/tables first and then in the parent form/table. Since, if I selected the Delete Record button in the Compensation tab/form after answering the ? "ok top delete, Then I can click the Delete Record button on the Demographics/parent form, and all works fine. When the user clicks the Delete Record button on the Demographics form (Top half), display a message like "do you want to delete this person and all the related data?", and then execute a sql to delete all related data, and finally go to a new record?

[RuralGuy]

I think what I really need to do is to delete all the records in the child forms/tables first and then in the parent from/table.

You are absolutely correct. As Steve said, it can be done in code with little trouble.

[jjz0512]

Thanks Steve, cascading deletes won't work. I have the EMRI key from the main form, that has the same value in all the child forms/data. So, I think your last suggestion will work, but what the code look like in VBA code?

[June7]

IMO, deleting records should be a rare event. Why do you want to delete? Why not just flag the parent record as 'inactive' or 'archived'?

However, code could be like:

CurrentDb.Execute "DELETE FROM maintable WHERE EMRI=" & Me.EMRI

Do an Execute for each table.

[jjz0512]

June7, thanks that worked great. Now I just need to place a confirmation before the deletes, to give them a chance to abort.

BTW; the tables don't have a flag to make inactive or active, but maybe in the future.

[jjz0512]

I was able to create the CASE for Delete Record where you delete the child records first via sql and the parent record last via a DoCmd.RunCommand acCmdDeleteRecord. This works fine for a new record added, but when I try to delete existing records, only the child records are deleted and I get a RunCommand action was canceled error message. Here's sample of my code.

Select Case MsgBox("Do you want to remove this record and all related tab records below?", vbYesNo, "ERMI Database")
    Case Is = vbYes
        CurrentDb.Execute "DELETE FROM ERMI_COMPENSATION WHERE ERMI_ID=" & Me.ERMI_ID
        CurrentDb.Execute "DELETE FROM ERMI_SITE_WORKED WHERE ERMI_ID=" & Me.ERMI_ID
        CurrentDb.Execute "DELETE FROM ERMI_PRIVILEGE WHERE ERMI_ID=" & Me.ERMI_ID
        CurrentDb.Execute "DELETE FROM ERMI_LICENSES WHERE ERMI_ID=" & Me.ERMI_ID
        CurrentDb.Execute "DELETE FROM ERMI_CME WHERE ERMI_ID=" & Me.ERMI_ID
        DoCmd.RunCommand acCmdDeleteRecord
        MsgBox "This record and any tab records have been deteted"
    Case Is = vbNo
        MsgBox "This record was not deleted"
End Select

[June7]

Why acCmdDeleteRecord? Why not use the CurrentDb.Execute for parent record as well?

Select Case maybe a little more code structure than needed.

If MsgBox("Do you want to remove this record and all related tab records below?", vbYesNo, "ERMI Database") = vbYes Then
...
Else
...
End If

[jjz0512]

June7, yes, I tried that and I get ODBC--delete on a linked table 'ERMI_DEMOGRAPHICS' failed. I also changed to a if statement as you suggested. It didn't make a difference. could it be an ODBC issue?