establishing table relationships.

[razdanrahul]

Hi,

Can someone please help me to establish one-to-many relationship with cascade update and cascade delete feature. I know how to do this in the table relationship window but am not sure how to get this in VB.

Thanks,

~R

 

[MarkK]

Assuming you have tables like...

[B]tParent[/B]
ParentID (PK - Autonumber)
ParentData

[B]tChild[/B]
ChildID (PK - Autonumber)
ParentID (FK - Long Integer)
ChildData

Then you can run code like...

Sub CreateRelationTest()
  Dim rel As DAO.Relation
   
  With CurrentDb
    [COLOR="Green"]'create the relation[/COLOR]
    Set rel = .CreateRelation( _
      "tParent_tChild", "tParent", "tChild", _
      dbRelationUpdateCascade + dbRelationDeleteCascade)
    [COLOR="green"]'append key field from parent table[/COLOR]
    rel.Fields.Append rel.CreateField("ParentID")
    [COLOR="green"]'point the relation at the foreign key in the child table[/COLOR]
    rel.Fields("ParentID").ForeignName = "ParentID"
    [COLOR="green"]'and append it to the database[/COLOR]
    .Relations.Append rel
  End With

End Sub

 

[razdanrahul]

Dear lagbolt,

Thanks a lot for the help. I tried this way of doing it but did not get the whole way through. I think I am missing a little insight because of which I am unable to replicate the my case. Details:

I have three tables:

tParent1
Parent1_ID1 (PK - string)
Parent1_ID2 (PK - string)
Parent1_Data

tParent2
Parent2_ID1 (PK - string)
Parent2_Data

tChild
Parent1_ID1 (FK - string)
Parent1_ID2 (FK - string)
Parent2_ID1 (FK - string)
ChildData

Could you tell me how can I set up the relationships in this case. Thanks a lot for your help.

~R

 

[razdanrahul]

I got it now. Thanks for the help.

 

[MarkK]

- I think that your tables tParent1 and tParent2 are too much the same for you to need both of them.
- It appears to me, given the links you're showing, that your tChild table actually functions as a parent. This seems confusing to me.
- I believe you need to troubleshoot your table relationships before moving ahead with this project.