[AccessD] Programmatically creating relationships

Seems my earlier post on this got missed, I think DAO-DDL in one line is
easier:

>>>>>>>>>>
Try something like this (DAO): 
    db.Execute "ALTER TABLE M_Employees ADD CONSTRAINT fk_Employee_Dept
FOREIGN KEY (Dept_ID) REFERENCES L_Departments (Dept_ID);" 

Steve 




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, March 28, 2007 10:07 AM
To: accessd at databaseadvisors.com
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com