Cascade Delete and Relationships 1

[saunan]

I'm setting up a Media Contact database in Access 2002 (converted from Excel data) and cascade deletes aren't working on two of my tables. I've got Referential Integrity and Cascade Updates established between all Primary Keys. All my forms properly update the proper tables (so far).

One of my forms can delete a record from tblRecipient. Cascade deletes are set on RecipNameID and SendAddrID, but the deletes don't occur on tblRecipName or tblSendAddr ... Anyone know why not?

Here are the tables and field names. Do I have them set up right?

tblOrg: List of unique organizations. Organizations can have multiple Recipients.
[blue]OrgID (PK), OrgName, OrgTypeID[/blue]

tblOrgTypes: Type of Organization (Press, Govt, University, etc.)
[blue]OrgTypeID (PK), OrgType[/blue]

tblRecipNames: Actual names of Recipients.
[blue]RecipNameID (PK), RecipName, OrgID[/blue]

tblRecipient: Each Recipient can belong to multiple Groups, and have multiple Addresses (fax number, email address and phone). This table provides a "many to many" relationship between RecipNames, SendAddr and Group (I think).
[blue]RecipID (PK), RecipNameID, SendAddrID, GroupID[/blue]

tblSendAddr: Unique fax numbers, email addresses, phone numbers.
[blue]SendAddrID (PK), SendTypeID, SendAddr[/blue]

tblSendType: FAX, Email or Phone
[blue]SendTypeID (PK), SendType[/blue]

tblGroup: Groups for geographic areas, University papers, etc.
[blue]GroupID (PK), GroupName, GroupDescr[/blue]

Many thanks!

 

[willir]

saunan

Smart idea having a M:M between recipeint and address.

In general, to accomplish a M:M you need a joiner or intermediary table that uses the Primary keys. It appears that tblRecipient accomplishes this with a twist...

[tt]
tblRecipient
tblRecipNames RecipID (PK)
RecipNameID (PK) ------> RecipNameID M:1 tblSendAddr
1:M SendAddrID -------> SendAddrID (PK)

[/tt]

To delete the SendAddress for a recipient, it must also remove entries in the Recipient table. Are the entries in the Recipient gone? Next, do you have a cascading delete setup from Recipient to Sender Address? And lastly, it is one thing to have the parent and then delete the children. It is another to find all the children and then delete the parent. I would have to read up on this aspect.

Richard

 

[saunan]

Richard:

Thanks for the reply. I just realized that different Organizations sometimes share the same SendAddress (newspaper chains, for instance). So I can't always delete the SendAddress when I delete a Recipient.

So ... the form that deletes records in tblRecipient DOES need to delete a record in tblRecipNames, but MAY need to delete a record in tblSendAddr -- only when the SendAddress is unique to that Recipient.

I think my form needs to change.

Current Form: Main form displays the Organization. SubForm displays all Recipients in that Organization (view = Continuous Forms). SubForm has a button which deletes an individual recipient. The proper record does get deleted from tblRecipient, but the related records do NOT get deleted in tblRecipNames and tblSendAddr.

New Form: Since tblSendAddr will not allow duplicates in SendAddr, perhaps the new form should use the SendAddr on the Main form. Subforms could display data from tblRecipient, tblRecipNames and tblOrg. Then, I could delete selected Recipients, and only delete the SendAddr if there is one related Recipient.

Does that sound right? I'll try this and post again with results.

 

[willir]

saunan

From your description, it sounds correct. I actually work M:M both ways. Access it by Address - these are the recipients in a contineous form. Access it by Receipient - these are the addreses in a contineous form. Same (joiner) table - almost the same subform except one of the fields is hidden. Next to each record in the contineous form, a small command button is used to open up the specific record in a single form.

Then, in the single record form based on the first foreign key, I may choose to display all other records also using the same foreign key.

As a personal preference, I have never been comfortable with cascading deletes. I prefer to use code for this since one could indvertantly blow away a lot of important info without realizing it.

To take the direction from Unix (a powerful OS)

"With power comes responsibility"

 

[saunan]

Richard: Thanks for your help. I've come to the conclusion that cascade deletes are too dangerous...

I designed a couple new forms, but still can't figure out how to delete only what needs deleting. I used a "record delete" command button, and a delete query with a macro that calls the query.

The command button worked best for deleting the record in tblRecipient.

The query/macro worked better for deleting records in tblRecipient AND tblRecipNames at the same time.

The problem remains the record in tblSendAddr. How could I program a function that does something like this:

The Easy Part: Delete record in tblRecipient and tblRecipNames where RecipNameID = CurrentRecord.

The Hard Part: Delete record in tblSendAddr where SendAddrID = tblRecipient.SendAddrID -- but only delete if SendAddrID is NOT used by another Recipient (or maybe if the Access user says it's okay to delete the SendAddress).

If the proper record in tblSendAddr does NOT get deleted, I'll end up with orphan records.

-- Steve

 

[willir]

saunan

Ensure that you have enabled referential integrity. This will prevent orphan records -- you have to delete the child records before deleting the parent.

 

[saunan]

OK. I think I've got the concept...

Form 1

Allows user to search for and display Media Contacts by Organization. No editing allowed. Command Button will open Form 2 for editing related records.

Form 2

Main form displays the SendAddress, the total number of Recipients for the address, and a Command Button for deleting the record from tblSendAddr (because of RI, this will only work if related Recipients are deleted first).

SubForm displays Recipients which use SendAddress. Editing the SubForm changes records in tblRecipient and tblRecipNames. Command Button calls RunSQL macro for deleting records in Recipient tables.

Thanks for your help!