Now here is a question I had myself many years ago and could never find a proper answer to! And yet it is such a basic element and such an important one at that!!!
So when one creates a split database (which they all should be), where do you create the tables relationships exactly? Do you create the MS Access table relationship within the Front-End or the Back-End?
The short answer is, in the Back-End. Here are a few explanation from a couple fellow MVPs taken from a recent forum question.
The relationships need to be established in the backend. In fact, you can build a diagram in the frontend but for the backend tables, the referential integrity will not be enforced if the relationships aren’t in the backend — Bob Larson, Access MVP
If you create relationships in the front end the only thing it achieves is that it determines the default joins types when you create a query in design view. To ensure data integrity through enforced relationships they must be created in the back end. So, the recent advice you were given is wrong. You can if you wish create them in the front end in addition to, but certainly not in place of, those in the back end. — Ken Sheridan
What is important to understand here is the fact that you should always create your table relationships in the Back-End of your database. That said, as Ken stated it can be useful to merely recreate them within the Front-End as well to simplify Query building, but then this then incurs extra overhead when the database is modified (Now you have to update the relationships in both locations). The other time when you might create MS Access table relationships within the Front-End of your database is because you have lookup table, reference tables in the Front-End. In such a case, obviously you can’t create the necessary table relationships in the Back-End since the tables don’t exists there. As such, you’d create the necessary relationships directly within the Front-End to ensure referential integrity.
