Join and update the tables

[matrik02]

I have two tables..and join the two tables 'A' and 'B' using Plot number field name. When I delete the Plot number in the tables 'A' Why the plot number still exits in the tables 'B' in Microsoft Access ? Why it cannot update?Any idea to solve this

[FunkyDexter]

I can't remember off the top of my head whether Access allows you to specify that deletes should be cascaded on a foreign key. If it does then you need to set that to on.

I don't think it does though and that means you're going to have to handle this from your code. That shouldn't be difficult, whenever you issue a delete against table A you need to issue a similar delete against table B.

[GaryMazzone]

You can set Access to Delete (or Update) on FK's (Cascade option). Personnaly I never use this option and always do this manually. If the FK was set properly to start with this would not happen (cascade is off by default) and the parent could not be deleted if a child is present

[FunkyDexter]

Generally I'd agree with Gary that you're probably safer to use the foreign key to refuse the delete instead of cascade it and then handle deletes in your code by deleting any child records first. This gives you much more control over what's happening in your database.

However, I have recently inherited a system from another company who control just about everything from within the database using triggers, sprocs etc. They made heavy use of foreign keys with a whole strategy surrounding enforcing integrity by both cascading or refusing deletes as apropriate. This works very well but does need alot of planning and documentation up front.