Access Expert 2

Expert Level 2 talks about Orphan records.

First check out Don't Delete Data.

In a multi user / split db environment, it is up to you to manage the records in other tables.
You are also probably going to run into issues with the global relationships window.  You will eventually find that you will have to delete some if not all of those relationships in order to make the split db work properly.
This is one of the reasons Richard shows you what it is but doesn't spend a ton of time maintaining it.

The Split Your Database video goes into some of this.

Ah ok, I guess splitting isn't an option in my case then since the join table relationships are fundamental to this solution. Thanks for the info.

Chris on the contrary.  You can still do the same thing.  It's just that you need to take the reins a bit more and through your code, whether Macros or VBA, do the checking and manipulation yourself.
The benefits and I would go so far as to say the necessity, in some cases, of having your data base split far out weighs the cascade deletes.  Just the security of your data is far superior.  Splitting is non negotiable in my opinion if you have more than one person using the database.

One thing you might want to consider is actually NOT deleting the data in the first place.  Check out Richard's Don't Delete Data video for some insight on how to handle things better.

I just reread your post about running this from a network and I officially will double down on the necessity of splitting your db.

Hi Scott thanks for the insight. I think what confused me is you said I would need to delete the relationships? I took this to mean my join tables would not function, which would render the solution useless. But I did a test split yesterday and everything still seemed to work.. I noticed the relationship badge became a mess but only visually, all the relationships still exist, and in the Back end file, all the cascading delete checks were still there.

Other side effect I noticed is some thing get pretty slow.. but that's probably because I use a lot of dlookups.