Referential Integrity Problem - Microsoft Access / VBA

Wayne

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?

Jun 26 '07 #1

Subscribe Reply

2654

Allen Browne

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*******@volc anomail.comwrot e in message
news:11******** **************@ x35g2000prf.goo glegroups.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?

Jun 26 '07 #2

Wayne

Thanks Allen. You've certainly given me some food for thought!

Jun 26 '07 #3

paii, Ron

"Wayne" <cq*******@volc anomail.comwrot e in message
news:11******** **************@ x35g2000prf.goo glegroups.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?

Put the lookup tables in the BE, have the FE update the descriptors when you
update the FE on the user machines.

Jun 26 '07 #4

Similar topics

1877

Disabling referential integrity

by: Sharon Cowling | last post by:

Hi, my problem is this: I have a table called faps_key the unique identifier being key_code: taupo=# \d faps_key Table "faps_key" Column | Type | Modifiers -----------------+-----------------------+----------- key_code | character varying(6) | not null date_key_issued | date |

PostgreSQL Database

3666

Deadlock. Referential Integrity checks select for update?

by: Grant McLean | last post by:

Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into "access_log", the referential integrity triggers generate these queries: SELECT 1 FROM ONLY "public"."application_type" x

PostgreSQL Database

2458

LOST REFERENTIAL INTEGRITY

by: Jimmie H. Apsey | last post by:

Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. Within that I have a table with referential integrity constraints which no longer work. I do not know how to disable referential integrity on a column in a table. I do not know how to view what Postgres thinks my referential integrity

PostgreSQL Database

4006

Verifying Referential Integrity

by: Geisler, Jim | last post by:

So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity. Are there any recommended methods or utilities for checking referential integrity in a PostgreSQL database?

PostgreSQL Database

1952

Impossible Relationship? Referential Integrity on tables with on From Date and To Date

by: HGT | last post by:

Hello all, I am currently on a project which the source data come into the databases is always dirty (not surprisingly); however, due to the design of the database, it is very difficult to impose referential integrity (esp. Foreign Keys) on these tables. Am I just not thinking straight? This solution is implemented on multi RDBMS. Example, we have 2 tables, one with a number of columns and a TIMEID column to specify the load date (in...

DB2 Database

4713

Referential Integrity - linking multiple tables to transaction table

by: heyvinay | last post by:

I have transaction table where the rows entered into the transaction can come a result of changes that take place if four different tables. So the situation is as follows: Transaction Table -TranId -Calc Amount Table 1 (the amount is inserted into the transaction table) - Tb1Id

Microsoft SQL Server

3770

Alter Table statment: adding foreign key WITHOUT referential integrity

by: moskie | last post by:

Is there a way to run an alter table statement that adds a constraint for a foreign key, but does *not* check the existing data for refrential integrity? I'm essentially looking for the equivalent to SQL Server's NOCHECK flag. I am able to create equivalent keys in the Relationships screen, as long as the "Enforce referential integrity" box is left unchecked. But when I try to create that relationship with an ALTER TABLE statement, I...

Microsoft Access / VBA

4495

Form/subform and referential integrity

by: CPAccess | last post by:

How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table? Here's the situation: I have two tables: tblContracts and tblPayments. tblContracts has an autonumber field called IDKey as its primary key. tblPayments also has an IDKey field (Integer datatype). The two tables are linked in a one-to-many relationship on the field IDKey with referential integrity...

Microsoft Access / VBA

3916

Referential Integrity problem. Related record is required in tableEmployee?

by: ApexData | last post by:

Access2000, using a continuous form. I’m getting a message that say “you cannot add or change a record because a related record is required in table Employee”. This occurs in all my combobox fields that have referential integrity linked to another table. I do not have the field in the table set as required, and I do not want to require the entry. If the record is filled out completely and added to the table, you can go back to the...

Microsoft Access / VBA

8221

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8162

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8662

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8603

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8463

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7134

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

5560

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

1769

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1468

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General