Referential Integrity Problem

[Sprinks]

I discovered today that, in a quick and dirty application that later turned
into an important one, I'd neglected to set up one-to-many relationships.
Predictably, I discovered orphan records in the detail table, which I've now
deleted.

The detail table and an inner join query of the two tables both return the
same number of records, indicating that all of the orphans are gone, yet
Access won't let me define the relationship with referential integrity
enforced. Does anyone know why, and what I can do to implement R.I.?

Thank you.

Sprinks

 

[Chriske911]

I discovered today that, in a quick and dirty application that later turned

into an important one, I'd neglected to set up one-to-many relationships.
Predictably, I discovered orphan records in the detail table, which I've now
deleted.

The detail table and an inner join query of the two tables both return the
same number of records, indicating that all of the orphans are gone, yet
Access won't let me define the relationship with referential integrity
enforced. Does anyone know why, and what I can do to implement R.I.?

Thank you.

Sprinks

first you create a relation without referential integrity
then create a query with the wizard for unmatched records

then you will be absolutely sure there are no orphaned records anymore

if so then you should be able to apply referential integrity

grtz

 

[KARL DEWEY]

You may have same quanity but still not match. Use an Unmatched query to
find the difference and fix accordingly.

 

['69 Camaro]

Hi, Kevin.

The detail table and an inner join query of the two tables both return the
same number of records, indicating that all of the orphans are gone, yet
Access won't let me define the relationship with referential integrity
enforced. Does anyone know why

The first thing that comes to mind is that one table is a linked table.
Relational database engines cannot enforce referential integrity on tables in
external files. Ensure that both tables are located within the current
database.

Next, ensure that the table on the one side of the relationship has a either
a primary key or a unique index that doesn't allow NULL's and is a required
field in that table. Use this "required" indexed field to establish the
relationship and enforce integrity between the records in the two tables.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
See http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Sprinks]

Gunny,

Thanks for the response. This indeed IS the problem; both tables are
linked. The origin of this arrangement is that I've created several
mini-applications, and to simplify their use as well as simplifying future
development, I've kept their tables in separate .mdb files, and linked them
into the main umbrella application.

But if I can't enforce RI, it's not worth it.

Congrats on your responder promotion.

Thanks.
Sprinks

 

[dbahooker]

you can't enforce RI, MDB is _CRAP_

use Access Data Projects against MSDE or SQL Server.

it works like a charm

 

[George Nicholson]

You can create and enforce referential integrity on those tables, but it has
to be done within the .mdb they actually live. Once done, those rules apply
to any app that links to that data.

So, as long as the tables are in the same file, and you have access to that
file, you should be able to do this.

HTH,

 

[dbahooker]

im sorry; i was thinking that he had multiple tables in multiple mdb
'backends'

lol

(mdb isn't really a backend; you have frontend with more frontends lol)

 

[Sprinks]

Thanks to all respondants; I learned something from all of you. I have
decided to alter my approach to place the relationship-dependent tables in
one central file, and have the satellite apps link them in. In that way, RI
is enforced in the central file, and I have the flexibility downstream.

Thanks, everyone.

Sprinks

 

[dbahooker]

again, your approach should be 'i dont trust mdb with my data' and if i
use SQL Server or MSDE i can keep all my data in one place and i can
have _real_ RI and triggers, scheduled jobs; real indexing--

mdb is just for sissies.. i mean-- limitations on critical pieces--
like RI in MDB-- is a symptom that MDB is for babies