Referential Integrity Unavailable

[Guest]

I'm using Access 2007.

I've created a nice little Access database with all the tables related to
each other and referential integrity are all enforced. I've split the
database with the backend holding the relationships with the referential
integrity still enforced. When I looked into the frontend and into the
relationships window, all the tables and the relationships are also there
with the referential integrity enforced.

Now I have the MS SQL Server 2005 Express installed and I've used the
upsizing wizard in Access to create a database into the SQL Server Express.
I've looked into the SQL database and the relationships between the tables
are still there. Since the one and infinity symbols are shown, I assume that
referential integrity are enforced just like in Access. Now, I looked into my
frontend Access database in the relationships window, all the tables are
still there (this is after the upsizing wizard) but with out the
relationships between them. I went to create the relationships and yes Access
recognizes the one-to-many relationship. However, I could not enforce
referential integrity--it's all grayed out. Even after purging the tables in
the SQL database of records, it's still won't let me enforce referential
integrity in my Access frontend.

How can I enforce referential integrity in Access after creating a backend
in MS ?SQL Server 2005 Express?

Many thanks for all the support.

Nils

 

[Ed Metcalfe]

I'm using Access 2007.

I've created a nice little Access database with all the tables related to
each other and referential integrity are all enforced. I've split the
database with the backend holding the relationships with the referential
integrity still enforced. When I looked into the frontend and into the
relationships window, all the tables and the relationships are also there
with the referential integrity enforced.

Now I have the MS SQL Server 2005 Express installed and I've used the
upsizing wizard in Access to create a database into the SQL Server
Express.
I've looked into the SQL database and the relationships between the tables
are still there. Since the one and infinity symbols are shown, I assume
that
referential integrity are enforced just like in Access. Now, I looked into
my
frontend Access database in the relationships window, all the tables are
still there (this is after the upsizing wizard) but with out the
relationships between them. I went to create the relationships and yes
Access
recognizes the one-to-many relationship. However, I could not enforce
referential integrity--it's all grayed out. Even after purging the tables
in
the SQL database of records, it's still won't let me enforce referential
integrity in my Access frontend.

How can I enforce referential integrity in Access after creating a backend
in MS ?SQL Server 2005 Express?

Many thanks for all the support.

Nils

I use Access 2003 and get behaviour with an Access backend.

I believe this is because referential integrity *must* be enforced by the
backend database (regardless of whether it is in Jet or SQL Server). If it
is enforced by the frontend anyone accessing the tables via a different
frontend (or direct into the backend) could bypass referential integrity and
make a complete mess of the data.

Ed Metcalfe.

 

[Nils Pettersson]

Thanks Ed. I understand now that referential integrity can only happen from
the backend. When you create your frontend you simply must know the
relationships from the backend to make it to work. And as long as referential
integrity is enforced, there is nothing to worry about.