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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|