Referential Integrity with MySQL Backend Database

[Peter Mollica]

I have an Access 2002 database that uses ODBC to connect
to the MySQL 3.5 database on our domain server. That
connection works great...however. When I originally
built the database I used local tables setting up
referential integrity in the Relationship window.
Everything was fine, updates were cascading properly. I
don't use the delete referential integrity.

My problem is that when I then changed the database for
the windows/reports from the local database to the MySQL
backend via ODBC, Access won't let me setup referential
integrity. I can build the relationships but
the "Enforce Referential Integrity" checkbox is disabled.

What am I doing wrong?

 

[John Vinson]

My problem is that when I then changed the database for
the windows/reports from the local database to the MySQL
backend via ODBC, Access won't let me setup referential
integrity. I can build the relationships but
the "Enforce Referential Integrity" checkbox is disabled.

What am I doing wrong?

Just doing something reasonable but in the wrong place. Access has no
way to force MySQL to enforce relational integrity on anything. The
tables and their constraints are outside of Access' control; bear in
mind, that if you DID define RI in your database, someone else could
open the MySQL tables directly or from a different frontend and do
something to violate RI, and your database would be unable to prevent
it.

You'll need to use the database administration tools in MySQL to
establish RI. Your frontend database will then inherit those
constraints.