MS Access – Tables – Where to Create Relationships? In the Front-End or Back-End?

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, as a general rule, 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 recreate the relationships within the Front-End 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).

Another exception to the rule might be when you create relationships within the Front-End because you have local lookup tables, 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.

The other exception to the general rule is when your front-end utilizes multiple back-ends, in such a case, you try to create as many relationships as you can in each back-end, but any relationships between each back-end needs to be created within the front-end.

7 responses on “MS Access – Tables – Where to Create Relationships? In the Front-End or Back-End?”

  1. Richard Beeks

    One of your friends at UtterAccess directed me here. I am very appreciated not only that you had a total explanation, You folks made it easy to understand and I am going to view you more often now that I found you.
    Thanks
    RAB

  2. Michele Fork

    I’m not sure why, but all my relationships in the backend of my split db have disappeared once I split the db. There is literally nothing in my relationships window on the backend. However, I do know that the relationships are being enforced as the db works on the front end. Now; however, I have just created two new tables and have to establish relationships. How can I do this on the backend? Do I have to recreate all relationships again?

    1. Daniel Pineault Post author

      Depending on how you split the database, I have seen such behavior before.

      One thing you could try is open the Relationships window, then right-click in the grey area of the Relationship window and click Show All.

      If the above does not display your relationships, then add the tables back to the back-end and then recreate the relationships.

  3. Michele Fork

    I split the db with using the instructions I found (Split DB wizard I believe) and after researching for about a week. I knew I would have updates to make, but didn’t think that would be a problem in terms of updating relationships. I tried to Show All as well, no luck. I can recreate the relationships, I just wanted to make sure that wouldn’t affect any of the existing data on the front end that we’ve already audited.

    thanks so much Dan.
    Michele Fork/Duke University Divinity School

    1. Daniel Pineault Post author

      No, it shouldn’t impact your data. That said, if your data doesn’t comply with the referential integrity, it will generate an error when you try to apply it.

      I’ve experienced databases whose relationships disappear into thin air, never managed to find the root cause. One of those ‘Microsoftisms’.

      Frequent backups is all I can say.