Creating table relationships with code

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • GGerard
    #1

    Creating table relationships with code

    Hello

    Does anyone know if it is possible with Access 2000 to
    create relationships between tables using code?

    Thanks
    G.Gerard


    Tags: None
    • Allen Browne
      #2
      Re: Creating table relationships with code

      Yes, here is an example:

      Sub CreateRelationD AO()
      Dim db As DAO.Database
      Dim rel As DAO.Relation
      Dim fld As DAO.Field

      'Initialize
      Set db = CurrentDb()

      'Create a new relation.
      Set rel = db.CreateRelati on("MyMainTable MyRelatedTable" )

      'Define its properties.
      With rel
      'Specify the primary table.
      .Table = "MyMainTabl e"
      'Specify the related table.
      .ForeignTable = "MyRelatedTable "
      'Specify attributes for cascading updates and deletes.
      .Attributes = dbRelationUpdat eCascade + dbRelationDelet eCascade

      'Add the fields to the relation.
      'Field name in primary table.
      Set fld = .CreateField("M yMainTableID")
      'Field name in related table.
      fld.ForeignName = "MyForeignKeyID "
      'Append the field.
      .Fields.Append fld

      'Repeat for other fields if a multi-field relation.

      End With

      'Save the newly defined relation to the Relations collection.
      db.Relations.Ap pend rel

      'Clean up
      Set fld = Nothing
      Set rel = Nothing
      Set db = Nothing
      Debug.Print "Relation created."
      End Sub

      --
      Allen Browne - Microsoft MVP. Perth, Western Australia.
      Tips for Access users - http://allenbrowne.com/tips.html
      Reply to group, rather than allenbrowne at mvps dot org.

      "GGerard" <ggerard@nbnet. nb.ca> wrote in message
      news:UGj%c.1236 20$Np3.5280598@ ursa-nb00s0.nbnet.nb .ca...[color=blue]
      > Hello
      >
      > Does anyone know if it is possible with Access 2000 to
      > create relationships between tables using code?
      >
      > Thanks
      > G.Gerard[/color]


        Comment

        • GGerard
          #3
          Re: Creating table relationships with code

          Hello and thank you Allen Browne for your reply

          As a follow up question can relationships be set between tables that reside
          in two different mdb?

          example : can a relationship be set between Table1 in First.mdb and Table2
          in Second.mdb?
          -how about if they are linked to a third mdb; can relationships(c ascade
          update and delete) be set between
          Table1 and Table2 in this third mdb?

          Thanks
          G.Gerard

          "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
          news:413dc809$0 $22803$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
          > Yes, here is an example:
          >
          > Sub CreateRelationD AO()
          > Dim db As DAO.Database
          > Dim rel As DAO.Relation
          > Dim fld As DAO.Field
          >
          > 'Initialize
          > Set db = CurrentDb()
          >
          > 'Create a new relation.
          > Set rel = db.CreateRelati on("MyMainTable MyRelatedTable" )
          >
          > 'Define its properties.
          > With rel
          > 'Specify the primary table.
          > .Table = "MyMainTabl e"
          > 'Specify the related table.
          > .ForeignTable = "MyRelatedTable "
          > 'Specify attributes for cascading updates and deletes.
          > .Attributes = dbRelationUpdat eCascade + dbRelationDelet eCascade
          >
          > 'Add the fields to the relation.
          > 'Field name in primary table.
          > Set fld = .CreateField("M yMainTableID")
          > 'Field name in related table.
          > fld.ForeignName = "MyForeignKeyID "
          > 'Append the field.
          > .Fields.Append fld
          >
          > 'Repeat for other fields if a multi-field relation.
          >
          > End With
          >
          > 'Save the newly defined relation to the Relations collection.
          > db.Relations.Ap pend rel
          >
          > 'Clean up
          > Set fld = Nothing
          > Set rel = Nothing
          > Set db = Nothing
          > Debug.Print "Relation created."
          > End Sub
          >
          > --
          > Allen Browne - Microsoft MVP. Perth, Western Australia.
          > Tips for Access users - http://allenbrowne.com/tips.html
          > Reply to group, rather than allenbrowne at mvps dot org.
          >
          > "GGerard" <ggerard@nbnet. nb.ca> wrote in message
          > news:UGj%c.1236 20$Np3.5280598@ ursa-nb00s0.nbnet.nb .ca...[color=green]
          > > Hello
          > >
          > > Does anyone know if it is possible with Access 2000 to
          > > create relationships between tables using code?
          > >
          > > Thanks
          > > G.Gerard[/color]
          >
          >[/color]


            Comment

            • Trevor Best
              #4
              Re: Creating table relationships with code

              GGerard wrote:[color=blue]
              > Hello and thank you Allen Browne for your reply
              >
              > As a follow up question can relationships be set between tables that reside
              > in two different mdb?
              >
              > example : can a relationship be set between Table1 in First.mdb and Table2
              > in Second.mdb?
              > -how about if they are linked to a third mdb; can relationships(c ascade
              > update and delete) be set between
              > Table1 and Table2 in this third mdb?[/color]

              No. Even if it did do this it is not enforcable and would be easily
              circumvented.

              --

              \\\\\\
              \\ \\ Windows is searching
              \ \ For your sig.
              \ \ Please Wait.
              \__\

                Comment

                Previous template Next
                Working...