Delete only FK on the many side and the record on the one side

[Bee]

Hi,

I want to Delete only FK on the many side first and the record on the one side by one click of a button. I wrote some code which sometimes works and sometimes it does not!!

I wonder if any one have a better idea or doing this please?

Private Sub Delete_Click()

Dim db As DAO.Database, rs As DAO.Recordset
Dim n As Integer, i As Integer
Dim vStart As Integer
Dim vEnd As Integer
Dim vSite As Integer
Dim vRCCID As Integer

vSite = Forms![frmSite].Form![SiteID]
vRCCID = Forms![frmSite]![Roads Construction Consent].Form![RCCID]
vStart = Me.PhaseStart - 1
vEnd = Me.PhaseEnd + 1

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPhase")
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
If n > 0 Then
    For i = 1 To n
        If rs![SiteID] = vSite Then
            If rs![PhaseNumber] > vStart And rs![PhaseNumber] < vEnd Then
                    rs.Edit
                    rs![RCCID] = Null
                    rs.Update

            End If
        End If
        rs.MoveNext
    Next i
End If
rs.Close
db.Close
Set db = Nothing
Set rs = Nothing

'/////////////////////////////////////////////

    DoCmd.RunSQL "DELETE RCCID FROM tblRCC WHERE RCCID = " & vRCCID & ""

'//////////////////////////////////////////////
End Sub

tblRCC is the one side of the relationship and tblPhase is the many side.

Any help will be very much appreciated.
B

 

[The_Doc_Man]

Other than probably violating all sorts of normalization concepts...

Placing a null in a record after the fact wouldn't work if you had relational integrity turned on UNLESS you also had a record with a NULL as its PK - which I don't think is legal. I'm betting that the intermittancy is because you have confused Access badly, perhaps even corrupted a couple of keys. Can you compact and repair this database?

I'm already guessing that relational integrity isn't turned on here. I don't think it possibly COULD be enabled, either for create or delete. So now comes the question... why are you doing this?

Here is the issue in my mind. You go through the trouble to declare a parent and child table relationship (one to many; same thing, different name) and now you want to orphan the child records at the same time as you dump the parent. This leaves orphaned records that now exist in a vaccuum. They essentially corrupt the purity of your table because now it contains both parented and parentless records. The moment you do this, you have destroyed the relational nature of the child table.

Technically speaking, I suppose you can do this when relational integrity is not enabled. If you are GOING to do this, the VBA code could have been done easier by just running TWO queries in a row, perhaps as a pair of DoCmd.RunQuery where you generated the SQL for both before running them.

One: Update the key field in the child tables where the key was the value you wanted to delete from the parent.

Two: Delete the corresponding parent.

But I'm still reeling from trying to decide the value in doing such a thing.

 

[KeithG]

Why do you need to delete the child records and then the parent?

 

[Banana]

If you have "Cascade Delete" enabled in the relationship, then you only need to delete the parent record, then boom! all children and the children of children are gone.

 

[Bee]

Other than probably violating all sorts of normalization concepts...

Placing a null in a record after the fact wouldn't work if you had relational integrity turned on UNLESS you also had a record with a NULL as its PK - which I don't think is legal. I'm betting that the intermittancy is because you have confused Access badly, perhaps even corrupted a couple of keys. Can you compact and repair this database?

I'm already guessing that relational integrity isn't turned on here. I don't think it possibly COULD be enabled, either for create or delete. So now comes the question... why are you doing this?

Here is the issue in my mind. You go through the trouble to declare a parent and child table relationship (one to many; same thing, different name) and now you want to orphan the child records at the same time as you dump the parent. This leaves orphaned records that now exist in a vaccuum. They essentially corrupt the purity of your table because now it contains both parented and parentless records. The moment you do this, you have destroyed the relational nature of the child table.

Technically speaking, I suppose you can do this when relational integrity is not enabled. If you are GOING to do this, the VBA code could have been done easier by just running TWO queries in a row, perhaps as a pair of DoCmd.RunQuery where you generated the SQL for both before running them.

One: Update the key field in the child tables where the key was the value you wanted to delete from the parent.

Two: Delete the corresponding parent.

But I'm still reeling from trying to decide the value in doing such a thing.

Here is the situation: tblRCC is the parent table of tblPhase; however, tblPhase records won't stand in a vacuum if they are orphaned because tblPhase is the parent of an other table. So cascade delete won't work here. The idea was to delete parent record from tblRCC and all its correspending FK in tblPhase keeping the child records not deleted and to avoid violations I wanted to delete the FK first before deleting the parent record.