Access not importing relationships for MySQL linked tables

Question

I have successfully linked my MySQL database with my Access database file. Everything is working fine except the relationships in the MySQL database are not appearing in Access.

I have made a plenty of relationships in the MySQL tables using foreign keys, but these relationships are not reflected in Access. Kindly help me to import the relationships from the MySQL database into Access.

Software I'm using: MySQL version 5, Microsoft Office 2013, Access file format: .accdb

Answer 1

While it is true that the MySQL foreign key constraints don't show up by default in the Relationships tab in Access, those constraints are still in place in MySQL and are still enforced for linked tables.

For example, say I have two MySQL tables, [customers] and [orders], with a foreign-key constraint on [orders]. If I link to those tables in Access and I try to insert a row into my [orders] linked table where the [customerID] does not match a [customerID] in my [customers] linked table the insert fails:

ODBC --insert on a linked table 'orders' failed.

[MySQL][ODBC 5.2(w) Driver][mysqld-5.5.29-0ubuntu0.12.04.2]Cannot add or update a child row: a foreign key constraint fails (`zzzTest`,`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerID`) REFERENCES `customers` (`customerID`)) (#1452)

You can go into the Relationships tab in Access and create "Access-side" relationships for the MySQL tables...

...but notice that the "Enforce Referential Integrity" options are greyed out because that is a function of the database setup at the server, not in Access. So really, the only benefits that the "Access-side" relationships would offer are:

• "documentation" of the relationships (which you could get from a database diagram generated against the MySQL database), and

• "automatic" joins between the linked tables in the Access query designer (which can also happen without [Access] Relationships if tables have columns with the same name).

It's up to you to decide whether it would be worth the trouble to create those "Access-side" relationships.

Answer 2

Since this is cross databases, it may have to recreated manually. Check out this one, Importing .sql into MS Access using OBDC