What table is affected by delete when form based on query?

Question

In MS Access 2003 I have a form whose record source is equal to a query that involves an INNER JOIN. The join is between a location table and a container table. Containers are objects that are stored in specific locations each location and container are specified by ID values.

SELECT DISTINCTROW Container.Container_ID, Location.Location_ID
FROM Location INNER JOIN Container 
ON Location.[Location_ID] = Container.[Location_ID]

What I am trying to figure out is this …. When I delete a record (using the form navigation controls) in the form based on the above query which tables are affected? Are records in the Container and Location tables deleted or is it just in the location table? Thanks!

Answer 1

It depends on whether or not you have a relationship set up between the tables, and your setting for "Cascade Delete Related Tables" is turned on for that relationship.

If you have a relationship, and Cascading Deletes are turned on, it will definitely delete the records in the associated table.

If Referential Integrity is enforced between the two tables, Access will complain if you try to delete a parent record without first deleting all of the associated child records.

It is unclear to me what happens in a form if both tables are present in a query for the form and a deletion is requested, and cascading deletes is turned off. But that would be easy enough to test.

http://office.microsoft.com/en-us/access/ha011739511033.aspx

Answer 2

Without a relationship defined, you will delete the current row from Container and leave Location unchanged.

However, I think you should add a relationship and enforce referential integrity to assure you can't delete a location which still has a container stored there. Otherwise you could wind up with a Container record which refers to a Location_ID which no longer exists. The relationship may not help with this form and query, but it can safeguard against mistakes in other situations.

There are other ways to design your form which would make it easier to understand which record will be deleted from where. You could base a main form on Location and include a subform based on Container. In the properties set Location_ID as the link master/child field. Then for the Location_ID displayed in the main form, all the containers stored at that location will be displayed in the subform. Use the subform to delete whichever container you want.