I'll give you a personal viewpoint on Access relationships and why they are good.
When you create queries, forms, or reports, you can often do so faster by use of a "wizard" - a bit of code that automatically builds things for you. Often, you find that the wizards appear to be dumber than a box of rocks.
Access is a Rapid Application Development (RAD) tool. If you pre-define distinct relationships to parent/child tables or lookup tables or whatever, the wizards suddenly "get smarter" because you have given them something to use to build your targeted object faster. I.e. they know another fact that often makes a huge difference in how they will build whatever they are building.
The relationships help ME too. When I see the relationship diagram and unravel the spaghetti, I am able to "see" how things fit together better because, let's face it, Man is a visually oriented animal. The relationship diagrams help in the visualization.
Now, another key feature - when declaring relationships, you can declare whether you do or do not want to enforce relational integrity that stops you from making bonehead blunders regarding creating phantoms or deleting valid records. (Trust me, I made all sorts of bonehead blunders before I learned how to prevent them.) It often saves you a lot of precautionary coding. You don't have to verify in your VBA code that you are avoiding a mistake because Access has code to do exactly that for you - as long as you remember to set the option in the relationship declaration.
In summary, what good is the relational part of Access? It's just another tool in the toolbox of things you might wish to do in a database applications suite. Nothing more, nothing less. But I'll also point out the old craftsman adage: If all you have in your toolbox is a hammer, everything gets treated like a nail. So by having more tools in the box, you can see the nails, nuts, bolts, and screws for what they really are.