I wouldn't cascade delete records either. I would rather tell users that they just cannot delete the ACME account because there are historical records using the account.
Did you read my explanation of how Cascade Delete works? When RI is enabled, it will PREVENT the deletion of parent records when there are child records in ANY related table unless Cascade Delete is checked for that relationship. It is up to YOU to understand the relationships and to apply Cascade Delete ONLY when appropriate. You might have 6 tables related to a parent. You are willing to allow the parent to be deleted even when there is data in 2 of the tables but if there is data in any of the other 4, then delete should not be allowed. Use cascade delete on 2 relationships but not on the other 4. It is not all or nothing.
Just saying you won't use Cascade Delete means that YOU have to write code in your forms to handle that situation. So, if accounts are related to 6 other tables, you need to run 6 dcount()s to determine that no related data exists. Then if you add a seventh table, you have to remember to modify the code.
Now if they really want a process that deletes sales history, price history, and so on for ACME, I can develop one, but I would rather the client thinks very carefully about it first, and then I will add a process to manage the deletes.
Cascade delete logically has nothing to do with this. You can make it as hard as you want to delete accounts. Only accounts that you allow to be deleted will be deleted, it is the Cascade that takes place as a result of that decision that you also need to now code. You MUST create a transaction and then inside the transaction, you would delete the related records from the 6 tables, then you would delete the account record. The transaction is necessary because you don't want some of the deletes to work if all of them don't work) But if you allow Cascade delete on any relationship where you would ever allow deletes of the parent, you let the database engine control the related process and that makes sure that your code doesn't miss anything.
Refusing to use well tested Jet/ACE code because you don't understand it is like taping up your left little finger to prevent its use because sometimes you hit the wrong key with it. Use the correct tool for the job. Cascade Delete is a very delicate tool. Very few relationships should use it but when they should, why make extra work for yourself?
Rather than making an arbitrary "rule" to never use a tool from your tool box, I tried to explain how you can logically determine whether the use of the tool is appropriate. Would you refuse to use your chainsaw because most of the time you don't want to cut down your trees?
Whether you use Cascade Update or not depends on what type of PK the table has. If the PK is an autonumber, you can't change it so no Cascade would ever happen so the point is moot. If you do use a changeable key, you then have a reason to check the Cascade update box but you are making a poor design choice when you use a changeable value as your PK. If you have a potentially changeable value you want to use as a PK, then it is best to use a unique index on the candidate key and substitute an autonumber that is used for relationships. I always do this when a "key" is coming to me from a different application. For example, I might interface with a human resources application and so their PK is EmployeeID. For now it is numeric but I don't have any control over it or whether it can change or not so I would make it a unique index and use a surrogate key in my application.
