A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Enforce Referential Integrity not available.



 
 
Thread Tools Display Modes
  #1  
Old December 17th, 2009, 09:41 AM posted to microsoft.public.access.tablesdbdesign
.Len B
external usenet poster
 
Posts: 81
Default Enforce Referential Integrity not available.

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.


  #2  
Old December 17th, 2009, 11:45 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Enforce Referential Integrity not available.

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


".Len B" wrote in message
...
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.


  #3  
Old December 17th, 2009, 01:10 PM posted to microsoft.public.access.tablesdbdesign
.Len B
external usenet poster
 
Posts: 81
Default Enforce Referential Integrity not available.

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.
"Allen Browne" wrote in message
...
| 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
| Tips for Access users - http://allenbrowne.com/tips.html
| Reply to group, rather than allenbrowne at mvps dot org.
|
|
| ".Len B" wrote in message
| ...
| 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.
|



  #4  
Old December 17th, 2009, 02:38 PM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Enforce Referential Integrity not available.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


".Len B" wrote in message
...
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.
"Allen Browne" wrote in message
...
| 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
| Tips for Access users - http://allenbrowne.com/tips.html
| Reply to group, rather than allenbrowne at mvps dot org.
|
|
| ".Len B" wrote in message
| ...
| 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.
|



  #5  
Old December 18th, 2009, 01:24 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Enforce Referential Integrity not available.

".Len B" wrote in
:

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/
  #6  
Old December 18th, 2009, 03:05 PM posted to microsoft.public.access.tablesdbdesign
.Len B
external usenet poster
 
Posts: 81
Default Enforce Referential Integrity not available.

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.
"David W. Fenton" wrote in message
36.91...
| ".Len B" wrote in
| :
|
| 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/



  #7  
Old December 18th, 2009, 11:49 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Enforce Referential Integrity not available.

".Len B" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.