How to include relationships when linking MySQL Database to MS Access?

Question

I've got a database in MySQL with about 40 tables, most of which have relationships between them. I'm attempting to build a frontend in MS Access with forms for CRUD operations. When I connect MySQL to Access using the ODBC connector, it successfully links the database and allows updates to be reflected from Access to MySQL Workbench and vice versa, however in the Table Relationships view in Access, these relationships are not reflected.

Since I'm under the impression that such relationships would be a good idea to have, how can I include those relationships in the linking process? Is such a thing possible, or will I have to manually re-add these relationships in the Relationship manager of Access?

Answer 1

Such a diagram really amounts to just pretty picture. When you drop the use of Access for the database, then all things like relationships etc. are NOW managed by the actual database system. So the diagrams and setup of relationships in access is not only ignored, but it really does not matter or change how MySQL is going to operate.

I mean, what would happen if you had 3 access front end (applications) all connected to the one MySQL system? Would you now have 3 different sets of relationships and they all work different? Who would now be the boss and set and enforce these rules?

So, all your table desings, rules, and relatnships are to be defined, created and managed by MySQL. Access in this kind of setup can only consume data, but it has ZERO say and ZERO to do with how MySQL or Oracle, or SQL server or whatever system you are consuming data with Access.

Because Access is only the consumer of data, then it has no more reflevance then when you browse books on amazon. The fact that you browse books or consume data from such systems does not mean out of the blue you now going to enforce relationships and rules on Amazon.

The exact same concepts apply to Access. The instant you adopt a different database is the same instant in which Access has zero control over the table designs, relationships and just about anything else in terms of how that particular brand "x" database system works.

So, any relationship setups etc. are handled 100% by the server system. Access at this point can only consume data and has no say in this matter in regards to relationships etc. In fact, access would not even know the specific and special commands to send to MySQL or Oracle or whatever system on how to build and setup such relationships.

So, Access can only setup relationships for Access. However, you are free to build forms, reports, and even build new queries. And the query builder lets you build and have a relationships between several tables, but building such a query never changed or told the actual database system how it going to handle relationships, nor even how they are to be setup.

So, Access as a client to such systems will 100% respect the relationships setup in such a database, and you gain zero by attempting to setup the relationship window in Access. you do get a pretty picture, but nothing more. So, use the tools and diagramming tools that come with the given database - Access has no say in this matter at all.