Enforce Referential Integrity not available.

[.Len B]

I have a db that will eventually be split.
tblChild has PK ChildID and is a linked table
tblNote has FK ChildID (long).
Relationship window will allow me to drop PK on FK and declares
a one-to-many relationship. The Referential Integrity check
boxes are unavailable. If I create the relationship anyway
both ends of the line have just a small blob rather than
a 1, many or arrow.

I am guessing there's nothing I can do about it and that it is
happening because the table on the one side is external. Right?

What happens when I split and the tables are in different
back ends? Am I in trouble?

 

[Allen Browne]

That's correct. Access is unable to enforce RI across different data files.

When you split your database, the relationships exist in back end.

 

[.Len B]

So I won't be able to create this relationship then because
each table will be in a different, the existing one and the
one from the split.

I suppose that means that I'll need to handle RI by joins
in queries.

--
Len
______________________________________________________
remove nothing for valid email address.
| That's correct. Access is unable to enforce RI across different data
files.
|
| When you split your database, the relationships exist in back end.
|
| --
| Allen Browne - Microsoft MVP. Perth, Western Australia
|
| Reply to group, rather than allenbrowne at mvps dot org.
|
|
| | > I have a db that will eventually be split.
| > tblChild has PK ChildID and is a linked table
| > tblNote has FK ChildID (long).
| > Relationship window will allow me to drop PK on FK and declares
| > a one-to-many relationship. The Referential Integrity check
| > boxes are unavailable. If I create the relationship anyway
| > both ends of the line have just a small blob rather than
| > a 1, many or arrow.
| >
| > I am guessing there's nothing I can do about it and that it is
| > happening because the table on the one side is external. Right?
| >
| > What happens when I split and the tables are in different
| > back ends? Am I in trouble?
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
|

 

[Allen Browne]

You can certainly create the joins, but you have to manage the integrity of
the data yourself: making sure you cannot delete/edit data in the primary
table that's referenced by the secondary, or insert/edit data in the
secondary that doesn't match the primary.

 

[David W. Fenton]

What happens when I split and the tables are in different
back ends? Am I in trouble?

Why would you do that? If the tables are sufficiently related that
it is necessary to enforce RI, then they belong in the same back-end
file. If you are splitting them up because your dataset is getting
too big, then you've outgrown Jet/ACE as a data store and should
instead use a more suitable back end, i.e., one that can handle more
than 2GBs of data.

 

[.Len B]

Hi David,
I am not splitting them up. They are already in two
different databases.

At the moment the 'new' database links to two tables in
a database created several years ago. The 'old' database
isn't split but it has been in the back of my mind on
and off that I should split it.

Earlier today I was thinking about splitting both
databases and combining the back ends. I came back here
to ask Allen if he foresaw any problems with doing that.
The 'old' database isn't large. The principal table has
almost 1,000 records. The 'new' is really an unknown
quantity but I expect it to outgrow the old in 12-18 months.

--
Len
______________________________________________________
remove nothing for valid email address.
| |
| > What happens when I split and the tables are in different
| > back ends? Am I in trouble?
|
| Why would you do that? If the tables are sufficiently related that
| it is necessary to enforce RI, then they belong in the same back-end
| file. If you are splitting them up because your dataset is getting
| too big, then you've outgrown Jet/ACE as a data store and should
| instead use a more suitable back end, i.e., one that can handle more
| than 2GBs of data.
|
| --
| David W. Fenton http://www.dfenton.com/
| usenet at dfenton dot com http://www.dfenton.com/DFA/

 

[David W. Fenton]

I am not splitting them up. They are already in two
different databases.

At the moment the 'new' database links to two tables in
a database created several years ago. The 'old' database
isn't split but it has been in the back of my mind on
and off that I should split it.

Earlier today I was thinking about splitting both
databases and combining the back ends. I came back here
to ask Allen if he foresaw any problems with doing that.
The 'old' database isn't large. The principal table has
almost 1,000 records. The 'new' is really an unknown
quantity but I expect it to outgrow the old in 12-18 months.

If the tables have data that is related to each other, they belong
in the same back end. I don't think that's an issue on which there
can be any dispute.