ADOX creation of relationship

[jamesdeckert]

I am creating a 1 to many relationship between 2 tables (in the back end). I
want the record on the parent table to be optional (0 or 1 record). While
creating the relationship programmatically I get an error. "You cannot add or
change a record because a related record is required in table "Diamond Vender
POW" (the parent table). The parent table is currently empty and the child
table contains data.
Parent table = [Diamond Vender POW]
Child table = [Diamond All]
linking field in both tables =[Vender POW]

The following is the creation of the relationship, I am leaving out the
creation of the table and primary key of [Diamond Vender POW] as they work
fine. [Diamond All] is an existing table.

Set cat = New ADOX.Catalog
Dim keyNew As New ADOX.Key
On Error GoTo TableERR
With keyNew
.Name = "DiamondVenderPOWDiamondAll"
.Type = adKeyForeign
.RelatedTable = "Diamond Vender POW"
.Columns.Append "Vender POW"
.Columns("Vender POW").RelatedColumn = "Vender POW"
.UpdateRule = adRINone
.DeleteRule = adRINone
End With
'
' append the foreign key
cat.Tables("Diamond All").keys.Append keyNew

There are a couple of things that are strange.

First the error occurs during the last line of code above (Append), but the
error handler for this function isn't called (TableERR). Instead the error
handler in the grandparent function is called (the parent function doesn't
contain an error handler).

Second, I can manually create the relationship in the back end and
everything works as I want. I added a form in the front end to display the
data and it works great.

I have another routine which does the same creation of a relationship but
the difference is that in this case I want the parent record to be optional.
In the other routine which creates the relationship fine, it is the child
table which is new.

any ideas would be greatly appreciated.
thanks,
James

 

[jamesdeckert]

I fixed the 'on error' problem.

I created a bogus table with no data to create the relationship and it
worked fine. EXCEPT the relationship had referential integrity checked. I
don't want RI, how do I specify no referential integrity? RI is the source of
this error, and I believe it will fix my problem to turn it off when the
relationship is created.

thanks,
James