ACCESS VBA - cannot create relations

Question

So take the following example:

Sub CreateRelation()

   Dim db As Database
   Dim rel As Relation
   Dim fld As Field

   Set db = CurrentDb
   Set rel = db.CreateRelation("OrderID", "Orders", "Products")

   'referential integrity
   rel.Attributes = dbRelationUpdateCascade

   'specify the key in the referenced table
   Set fld = rel.CreateField("OrderID")

   fld.ForeignName = "OrderID"

   rel.Fields.Append fld

   db.Relations.Append rel

End Sub

I keep getting the error:

No unique index found for the referenced field of the primary table.

If I include the vba before this sub to create in index on the field, it gives me the error:

Index already exists.

So I am trying to figure this out. If there are not any primary keys set, will that cause this to not work? I am confused by this, but I really really want to figure this out. So orderID is a FOREIGN KEY in the Products Table

Answer 1

You can create a relationship manually through the interface, then reverse engineer it. Here is an Immediate Window session where I examined an existing relationship:

? currentdb.Relations.Count
 1 

? currentdb.Relations(0).Name
parentchild

? currentdb.Relations(0).Table
tblParent

? currentdb.Relations(0).ForeignTable
tblChild

? currentdb.Relations(0).Fields.Count
 1 

? currentdb.Relations(0).Fields(0).Name
id

? currentdb.Relations(0).Fields(0).ForeignName
parent_id

For reference, this is the sub used to create that relationship:

Public Sub CreateRelationship()
Dim strSql As String
strSql = "ALTER TABLE tblChild" & vbNewLine & _
    "ADD CONSTRAINT parentchild" & vbNewLine & _
    "FOREIGN KEY (parent_id) REFERENCES tblParent (id);"
CurrentProject.Connection.Execute strSql
End Sub

Don't bother with the dbRelationUpdateCascade attribute until after you confirm you have sorted out which are the foreign table and key.

Answer 2

The error "No unique index found for the referenced field of the primary table" is caused by the fact that OrderId must not be the primary key of the Orders table and it must not have a unique index on it.

The other errors "Index already exists" are caused by the fact that Access will add a non-unique index to a foreign key when it is created. It is likely that there is already an index in the Products table called "OrderId" and it is creating a collision when you try to create the foreign key via code. You need to check the TableDef.Indexes collection on the Products table to ensure that an index called "OrderId" does not already exist before you add the foreign key.