Referential Integrity Problem cqdigital[ at ]volcanomail.com 6/26/2007 6:18:50 AM 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?

Re: Referential Integrity Problem "John Spencer" <spencer[ at ]chpdm.edu> 6/26/2007 11:05:16 AM I would still put the tables in the backend and use VBA code routine to update the table values when launching the (new) front end. Using the database engine is the only way you can reliably enforce referential integrity and in my opinion this is the best way to handle the situation. Other options are careful design to make sure that the users can never enter data directly into the tables (only through forms). -- All forms must use comboboxes (or listboxes) for entering the data. -- All data imports (if any) must carefully check the data to make sure there is no invalid values. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. <cqdigital[ at ]volcanomail.com> wrote in message news:1182838730.285639.282360[ at ]g37g2000prf.googlegroups.com... [Quoted Text] > 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? >

Re: Referential Integrity Problem "Tony Toews [MVP]" <ttoews[ at ]telusplanet.net> 6/27/2007 2:49:54 AM "Tom Wussernark [MSFT]" <tw[ at ]microsoft.com> wrote: [Quoted Text] >look buddy > >linked tables suck a big fat cock > >you should move to ADP if you want DRI Note that this person is really A a r o n K e m p f and that he is not an employee of Microsoft. And needless to say no Microsoft employee would use such language. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Re: Referential Integrity Problem "Chris2" <rainofsteel.NOTVALID[ at ]GETRIDOF.luminousrain.com> 6/27/2007 5:28:03 AM <cqdigital[ at ]volcanomail.com> wrote in message news:1182838730.285639.282360[ at ]g37g2000prf.googlegroups.com... [Quoted Text] > I'm building a database that consists of frontend and backend. Some > of the lookup tables need to reside in the frontend database. cqdigital, Why? > I may need to update the descriptors periodically, hence > the need to keep these tables in the frontend database This makes it seem like the data should be in the backend database and exist in the frontend databases as linked tables . . . > so that the new descriptors will be distributed with frontend updates. .. . . which would accomplish this without additional effort. > 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? > As long as instant updates are not necessary, you could think of putting code in each frontend database that ran on open that checked for updates and copied them over. Otherwise, you will need to use your backend to programmatically update all frontend databases whenever you make a change. Ideally, this would always be run when no users are accessing the backend. (No referential integrity would be available in either case.) Sincerely, Chris O.