Re: Referential Integrity on relationship with Multiple fields

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Peter Johal (pjohal02_at_hotmail.com)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 04:11:08 -0800

Allen,

I've created a relationships database that shows what I can and cannot do. I can send it to you to see if you can do what I cannot. Zipped it is just 10K.

Peter
     
     ----- Allen Browne wrote: -----
     
     Just tested that in 2 steps, and no problem here.
     
     Deleted my relation.
     Added an entry to Table1 just to be sure.
     Created the 2-field relation without RI.
     Re-opened the Relationships window, double-clicked the relation, and checked
     the RI box.
     
     Wonder what's different?
     
     (Will check back tomorrow: it's 2am here.)
     
     --
     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.
     
     "Peter Johal" <pjohal02@hotmail.com> wrote in message
     news:77E0E8BC-9B85-4A0F-AB3E-72132A3141CB@microsoft.com...
>> I'm using the same jet engine. And tried to create the relation on an
     access 97 database, but again I got the same result.
>> The relationship I'm trying to create is exactly as you discribe it. But I
     want to emphasize that I am able to create the relationship. I am just
     unable to turn on the option to enforce referential integrity. I do it in
     two steps: first I create the relationship and save. Then I try to turn on
     referential integrity and I get the error message.
>> Peter
>> ----- Allen Browne wrote: -----
>> Yes: created in A2003.
> Just tested a pair of 30-char fields.
>> Don't know if it makes a difference, but I am using Jet 4 SP8, i.e.
> msjet40.dll is version 4.0.8015.
>> You are creating the relation on the combination of the 2 fields?
     i.e. in
> the Edit relations dialog, you have:
> Table/Query Related Table/Query
> Table1 Table2
> F1 F1
> F2 F2
> where Table1 is the one that has F1 + F2 as primary key.
>>> "Peter Johal" <anonymous@discussions.microsoft.com> wrote in message
> news:7C5FD2B1-FAA8-421C-A9C9-C521E01DFDBB@microsoft.com...
>>> I have two text fields length 30 in both tables. Data type and
     size match.
> And there are no hidden indexes, I used your count statement: I a
     count of
> 1.
>>> I created two dummy tables from scratch and tried to enforce
     referential
> integrety on again on a relationship with two fields, but again with
     the
> same result.
>>> Then I changed the datatype on the first field on both tables to
> autonumber/ numeric. But again I got the same result.
>>> Are you using access 2003?
>>> Peter
>>> ----- Allen Browne wrote: -----
>>> Just tested here, Peter, and the relationship was created
     fine.
>> The example I tested used a Number (Long) + Text (20-char) in
     both
> tables.
>>> Make sure your field pair match on data type and size in both
     tables.
>> Remove any spurious indexes on the table, including any hidden
> indexes.
>> You may need to test this programmatically:
>> ? CurrentDb().TableDefs("YourPrimaryTable").Indexes.Count
>>> "Peter Johal" <anonymous@discussions.microsoft.com> wrote in
     message
>> news:973C56DA-A7FB-4B8A-8418-6FE7B50F4CCA@microsoft.com...
>>> I cannot turn on referential integrity on a relationship
> (parent-child)
>> with two field. Access gives the error message (translated
     from
> dutch):
>>>> No unique index has been found on field of primary table that
> is
>> references. (Error 3609)
>>>> However, the primairy key of the primary table consist of
> precisely the
>> two field in the relationship. So there is a unique index on
     those
> two
>> fields.
>>>> I'm using access 2003. Is this an error or a limition of access?
> What can
>> I do?
>>>> Peter