ACCESS VBA - DAO in VB - problem with creating 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")

'refrential 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 vb 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 not to 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

please help thanks justin

Answer 1

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.

Answer 2

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.