CreateRelations

[irish634]

I'm having a little trouble getting this to work...
I'm trying to create a one to many relationship with both update and delete cascading.

The code runs with no errors, but the relationship is not shown in the relationship window as being created.

Does anyone have any pointers?

    Dim DBS As DAO.Database
    Dim TBL As DAO.TableDef
    Dim FLD As DAO.Field
    Dim REL As DAO.Relation

    'Create the relationship for subtbl_BAPPTracking
    Set DBS = OpenDatabase(txt_Path)
    'Create a new relation
    Set REL = DBS.CreateRelation("JOBBAPP")
    'Define its properties
    With REL
        'Specify the Primary Table
        .Table = "tbl_JobTracking"
        'Specify the Related Table
        .ForeignTable = "subtbl_BAPPTracking"
        'Specify attributes for cascading updates and deletes.
        .Attributes = dbRelationUpdateCascade And dbRelationDeleteCascade
     
        'Add the fields to the relation.
        'Field name in primary table.
        Set FLD = .CreateField("lng_RecordID")
        'Field name in related table.
        FLD.ForeignName = "lng_tblRecordID"
        '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.
    DBS.Relations.Append REL
    DBS.Close

 

[irish634]

OK I got it to work using the following code:

    Dim DBS As DAO.Database
    Dim TBL As DAO.TableDef
    Dim FLD As DAO.Field
    Dim REL As DAO.Relation
    Dim WKS As DAO.Workspace
    
    Set WKS = DBEngine.Workspaces(0)
    Set DBS = WKS.OpenDatabase(txt_Path)
    Set REL = DBS.CreateRelation("JobBAPP", "tbl_JobTracking", "subtbl_BAPPTracking", dbRelationUpdateCascade Or dbRelationDeleteCascade)
    Set FLD = REL.CreateField("lng_RecordID")
    FLD.ForeignName = "lng_tblRecordID"
    REL.Fields.Append FLD
    DBS.Relations.Append REL
    DBS.Relations.Refresh

The relationship is successfully created. However.... The relationship window does not show the relationship until I actually "Show Table" and save it.

The relationship is in fact created as verified in the MSysRelationships table, just won't appear in the relationship window until I manually show it.

Is there a way to do this (show the tables) dynamically?

Thank you,
Craig