referential integrity question

[djc]

Hello all,

I have a utility (not really big enough for me to call an app) that I use to
manage an area of a database. Its done in access2002. Its an mdb file with 2
jet-conntected ODBC tables from an SQL2000 server. The setup is very simple.
1 table, lets say called customers, has 1 record for each customer...
cutomerID field as key and of course some other non-key fields as well. The
other table, called products, has just 2 fields, customerID and productID. I
want to define a 1 to many relationship between the two so I can use
cascading updates and deletes however upon defining the relationship the
check boxes to enforce referential integrity and cascading updates/deletes
are grayed out (disabled).

Am I not supposed to be able to use this with my setup because they are ODBC
connected or something?

any help is appreciated.
Thanks.

 

[Rick Brandt]

Hello all,

I have a utility (not really big enough for me to call an app) that I use to
manage an area of a database. Its done in access2002. Its an mdb file with 2
jet-conntected ODBC tables from an SQL2000 server. The setup is very simple.
1 table, lets say called customers, has 1 record for each customer...
cutomerID field as key and of course some other non-key fields as well. The
other table, called products, has just 2 fields, customerID and productID. I
want to define a 1 to many relationship between the two so I can use
cascading updates and deletes however upon defining the relationship the
check boxes to enforce referential integrity and cascading updates/deletes
are grayed out (disabled).

Am I not supposed to be able to use this with my setup because they are ODBC
connected or something?

Correct. You can only enforce RI on local tables.

 

[Albert D. Kallal]

The front end has no bearing on what the back end data settings are for
referential integrity.

If you try and change the relationships in the front end, and try to
"enforce" things, you will find that none of the settings work. This fact
even applies to when you are using just JET and a front end, and a back end.

The real rule here is that you ALWAYS SET the RI stuff in the back end. Of
course if you had 5 different users, and they all could change the RI stuff
in the FRONT END, then you can see how that would be a huge confusion of a
mess. You could never enforce RI in the front part, and never could for
linked tables.

Thus, you ALWAYS set the RI stuff in the back end. So, in your case, you
need to set the RI stuff in back end database. That back end database might
be Oracle, sql2000 or a JET mdb file share. Regardless, you set the RI stuff
where the data is, not the front end.

 

[djc]

Thanks.

Could I then setup/enforce referential integrity on the SQL2000 server then?

 

[djc]

one more question.

Can you enforce referential integrity with linked tables that are linked
from another access mdb file?

 

[djc]

well that answers the ?'s I just got done posting... : )

Thanks!

 

[djc]

nevermind.

Thanks.

Could I then setup/enforce referential integrity on the SQL2000 server then?

 

[djc]

nevermind.

one more question.

Can you enforce referential integrity with linked tables that are linked
from another access mdb file?