Delete query problem.

[JGalletta]

What am I doing wrong here???

DoCmd.RunSQL "DELETE tblScoutingRecords.*, tblScoutingRecords.[Record Number], tblFieldRecJoin.*, tblFieldRecInfo.* FROM (tblScoutingRecords INNER JOIN tblFieldRecJoin ON tblScoutingRecords.[Record Number] = tblFieldRecJoin.[Record Number]) INNER JOIN tblFieldRecInfo ON tblFieldRecJoin.JoinPK = tblFieldRecInfo.JoinPK WHERE (((tblScoutingRecords.[Record Number])=[Forms]![frmScoutingRecords]![Record Number]))"

It's giving me a "Run-time error '3128': Specify the table containing the records you want to delete."

Does this mean I cannot delete all of this stuff in this manner?

 

[boblarson]

Does this mean I cannot delete all of this stuff in this manner?

That's correct. You must delete from one table at a time.

 

[Pat Hartman]

If the tables have a hierarchical relationship and you have specified Cascade Delete, deleting a row from the highest level table will cause the child records in the other tables to also be deleted.

 

[JGalletta]

Pat, will enabling cascade delete only allow deletion in the downward direction of a hierarchical structure? i.e. Deleting a record from the second tier of a hierarchy will only delete records in the third, fourth, fifth, ... tiers, and not the first tier? If so, that function will prove useful for other parts of my application. Not knowing this is the only reason I haven't enabled it thus far.

 

[Pat Hartman]

Cascade Delete only deletes down. Your delete query needs to specify the highest level of the delete. So you have t1-->t2-->t3-->t4
Delete * from t1 Where PK = 1; -- will delete the t1 record and all related records in t2, t3, and t4.
Delete * from t2 Where PK = 1; -- will delete the t2 record and all related records in t3 and t4.
Delete * from t4 Where PK = 1; -- will delete only the t4 record.

Cascade Delete is only appropriate on hierarchiacal relationships. Do not specify it for lookup relationships. For example, you would not specify Cascade delete on the relationship between tblState and StateCD in tblAddress. If you accidentally deleted CT, that would also delete all Connecticut addresses and you will be very unhappy. You do want to select the Enforce RI option though. That way if you try to delete CT, Access will refuse to do so if there are any related records in tblAddress.