Enforce Referential Integrity not available.
in [Tables]
|
From: .Len B on 17 Dec 2009 03:41 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.
From: Allen Browne on 17 Dec 2009 05:45 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" <gonehome(a)internode0.on0.net> wrote in message news:#Yy00SvfKHA.6096(a)TK2MSFTNGP02.phx.gbl... > 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.
From: .Len B on 17 Dec 2009 07:10 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" <AllenBrowne(a)SeeSig.Invalid> wrote in message news:eJQrTYwfKHA.1536(a)TK2MSFTNGP06.phx.gbl... | 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" <gonehome(a)internode0.on0.net> wrote in message | news:#Yy00SvfKHA.6096(a)TK2MSFTNGP02.phx.gbl... | > 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. |
From: Allen Browne on 17 Dec 2009 08:38 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" <gonehome(a)internode0.on0.net> wrote in message news:u3JwwHxfKHA.2164(a)TK2MSFTNGP02.phx.gbl... > 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" <AllenBrowne(a)SeeSig.Invalid> wrote in message > news:eJQrTYwfKHA.1536(a)TK2MSFTNGP06.phx.gbl... > | 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" <gonehome(a)internode0.on0.net> wrote in message > | news:#Yy00SvfKHA.6096(a)TK2MSFTNGP02.phx.gbl... > | > 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. > | > > >
From: David W. Fenton on 17 Dec 2009 19:24 ".Len B" <gonehome(a)internode0.on0.net> wrote in news:#Yy00SvfKHA.6096(a)TK2MSFTNGP02.phx.gbl: > 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/
|
Next
|
Last
Pages: 1 2 Prev: Set up db to lookup and concatenate large amounts of text Next: Food Pantry Database |