Referential integrety problem

[glenmac]

I'm trying to set up relationships in an Access db that has linked tables to a MYSQL db. When I try, referential integrety is greyed out. Does anyone know away around this problem as I need onupdate/ondelete cascade to work. I've tried using relationships in MYSQL but get errors. When I try to insert data in the linked tables. I can insert data with no relationships set up in MySQL but don't have the benefit of referential integrety. I've posted this question in another forum by mistake. Sorry for my mistake. I hope this is the right forum. All help would be greatly appreciated.

 

[LittleSmudge]

Access cannot maintain Relational integrity across links.

It cannot even manage it if you link an Access back end database to another Access Front end.

You need to do the Cascade Update and Cascade Delete in code yourself
Cascade Update is just a selective Update Query and
Cascade Delete is just a selective Delete Query
- you just have to notice the Primary value changing in the Form and run the code yourself.



'ope-that-'elps.


::: Now - my turn to quiz you - Are you suggesting that you have LINKED a MySQL table directly into Access ?
I didn't think this was possible. If you have - please explain HOW. What's the bit I have missed ?



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Steve101]

Suggest you control the ref integrity from the MySQL backend. The fact that you're getting errors when you try to set this up suggests that you may already have data in the tables which violates the RI, and/or the primary / foreign key definitions are not consistent with defining the RI. Review this, as its worth getting RI working from the backend (if you can), in preference to trying to force it from the client end. It does offer greater protection of the data.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[LittleSmudge]

I'm not a MySQL expert - so take this next bit with an accuracy warning .. ..

I was under the impression that MySQL did not do Referential Integrity at all.
I thought the developer had to ensure referential integrity outside of the data storage structure

Is this the case - or have I miss read the manual somewhere ?





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[glenmac]

Thanks for the replies. As I'm new to Access this was a great help. GLS yes you can set up relationships in MYSQL with the later versions, using the tables types BDS (Berkly)or INNODB. Is you want more information go to

http://www.mysql.com/doc/en/example-Foreign_keys.html

Thanks again folks.