[AccessD] Programmatically creating relationships

Hi John

Here's a skeleton for DAO:

    strTable = "tblMaster"
    strForeign = "tblChild"
    strRelation = strTable & "_" & strForeign
    booExists = IsRelation(strFile, strRelation)
    If booExists = False Then
      Set rlt = dbs.CreateRelation()
      With rlt
        .Name = strRelation
        .Table = strTable
        .ForeignTable = strForeign
        .Attributes = 0
        Set fld = .CreateField("ID")
        fld.ForeignName = "MasterFK"
        .Fields.Append fld
      End With
      With dbs.Relations
        .Append rlt
        .Refresh
      End With
      booExists = IsRelation(strFile, strRelation)
    End If
 
Public Function IsRelation( _
  ByVal strDatabase As String, _
  ByVal strRelation As String) As Boolean

  Dim dbs       As Database
  Dim lngCount  As Integer
  Dim booFound  As Boolean
  
  On Error GoTo Err_IsRelation
  
  If Len(strDatabase) = 0 Then
    Set dbs = CurrentDb()
  Else
    Set dbs = DBEngine(0).OpenDatabase(strDatabase)
  End If
  
  lngCount = dbs.Relations.Count
  While lngCount > 0 And Not booFound
    booFound = (StrComp(dbs.Relations(lngCount - 1).Name, strRelation, vbTextCompare) = 0)
    lngCount = lngCount - 1
  Wend
  
  Set dbs = Nothing
  
  IsRelation = booFound

Exit_IsRelation:
  Exit Function
 
Err_IsRelation:
  IsRelation = False
  Resume Exit_IsRelation

End Function

Note that a hidden index will be created for the FK even if an index for this already is present.
Also note, that this code does not handle compound indices as is; if needed, modify it to accept the additional field(s) of the index for the relation.

/gustav


> For example, I have written a function that finds orphaned records in a
> child table and deletes them out.  What I need to do next is establish a
> relationship between the parent / child.  I can write that code but have
> never done so and would use borrowed code if anyone out there has a lib of
> such relationship oriented functions.  Given a parent table / PK and a 
> child table / FK, establish a relationship.
>
> Does anyone have that?
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com