Since you cannot use Referential Integrity across different files, you will
have to manage it manually.
That means you intercept each insert and edit into the back end table, and
ensure that it matches one of the values in the front end. Similarly, you
must intercept each edit and delete in the front end table to ensure this
leaves no orphan in the back end.
There is still no guarantee that this will work okay. For example, if Fred
inserts a new value (say 93) into his front end, and then assigns that value
as a foreign key value in the back end, when Jenny opens her front end and
has *not* inserted 93 there, you have an orphaned record situation.
--
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.
"Wayne" <cq*******@volcanomail.comwrote in message
news:11**********************@x35g2000prf.googlegr oups.com...
I've inadvertently placed this post in another similar newgroup, and I
apologise if you get it twice.
I'm building a database that consists of frontend and backend. Some
of the lookup tables need to reside in the frontend database. The
data in the lookup fields that relate to the backend tables will
never
change, but a descriptor for items in these fields will. I may need
to update the descriptors periodically, hence the need to keep these
tables in the frontend database so that the new descriptors will be
distributed with frontend updates.
Ideally the lookup fields should be linked to the backend tables with
referential integrity enforced. Because the tables are in 2
different
databases this can't be done.
What options do I have? Given that the lookup fields will always be
under my control and will never change, is enforcing referential
integrity really an issue?
