No Unique Index Found

[Guest]

Professionals: I am trying to manually set relationships between several
tables. I am able to create a relationship however between a couple of the
tables I get the message "no unique index found for the referenced field of
the primary table" when I try to click the boxes to enforce referential,
cascade update, and cascade delete.

Also, it shows the relationship as intermediate between the tables I am
having problems with.

I do have primary keys set up in the tables so there are unique fields (or a
combination of fields make up the primary keys in some instances).

Thanks in advance.

 

[Allen Browne]

The make an index with enfored RI, the matching fields fields need to be:
- of the same data type, and
- of the same size (e.g. Text (24) matching Text (24), or AutoNumber
matching Long Integer).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Thank you for your response Allen. This did not work. Data type is the same
and all fields concerned are the same length and I continue to get the same
message.

 

[Allen Browne]

Unless the datatbase is damaged/corrupted, there must be an issue such as:
- trying to create the relationship the wrong way around (i.e. wrong table
selected as the primary table in the Create Relation dialog);
- mismatching fields;
- specifying them in the wrong order (i.e. the order you listed the fields
of the multi-field index in the Create Relation dialog does not match the
order of the fields in the multi-field primary key.

If it helps, this code will list the indexes for a table:

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IIf(ind.Primary, "Primary", ""), _
IIf(ind.Foreign, "Foreign", ""), ind.Fields.Count
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message