Re: Referential Integrity on relationship with Multiple fields

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 03/18/04 

Date: Fri, 19 Mar 2004 00:49:37 +0800

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. 

-- 
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" <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

Relevant Pages

  • Re: Celebrate Evolution! today
    ... See references above. ... Christians were at loggerheads, struggling for power. ... on the battle which has drawn praise is "St. Paul vs. St. Peter" by ... of Internal evidence, external evidence, and extant documentation ...
    (misc.news.internet.discuss)
  • Re: Referential Integrity on relationship with Multiple fields
    ... "Peter Johal" wrote in message ... unable to turn on the option to enforce referential integrity. ... referential integrity and I get the error message. ... So there is a unique index on ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Using same Class in several projects
    ... Pro: Can call directly functions in normal modules and existing object ... establish the references in the first place. ... Have you got a personal preference Peter? ...
    (microsoft.public.excel.programming)
  • Re: In Visual Basic 2008 create aspx web pages programmatically
    ... Peter K ... I wrote a program to create the folders & place the images into the ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Strange goings on with VS2003 & System.TypeLoadException
    ... One thing to mention maybe...my project references a number of other class ... > Hi Peter, ... > Full details of one of the exceptions is shown below: ... > Additional information: Could not load type ...
    (microsoft.public.dotnet.framework.compactframework)