specify behavior of referential integrity using code 1

[knot22]

Hi,

I am wondering if there is a way to set "Cascade Update Related Fields" and "Cascade Delete Related Records" using code rather than editing the relationship and manually checking the boxes. In the data model I'm creating, all of the tables, primary and foreign keys, unique constraints, validation rules etc. are defined using a combination of inline SQL (for DDL) and VBA. The only thing that's missing is how to set the properties of the foreign key. Access gives a run-time error of '3289': "Syntax error in CONSTRAINT clause" on this inline statement:

ALTER TABLE InvoicesHeader ADD CONSTRAINT FK_InvoicesDetail_InvoicesHeader
FOREIGN KEY (InvoiceHeaderID)
REFERENCES InvoicesHeader (InvoiceHeaderID)
ON DELETE CASCADE
ON UPDATE CASCADE

However, when the last two lines are removed (ON DELETE CASCADE DELETE and ON UPDATE CASCADE) it runs fine. Does anyone know how to set the referential integrity properties in a module?

Thanks in advance, knot22

 

[MajP]

Use the Attributes property to determine whether the relationship between fields in the table is one-to-one or one-to-many and how to enforce referential integrity.

Use the Attributes property to determine whether the Microsoft Access database engine can perform cascading update and cascading delete operations on primary and foreign tables.

Use the Attributes property to determine whether the relationship between fields in the table is left join or right join.

So the answer is yes. Here is some helpful code and there is some in the help file

Public Function CreateRelation(primaryTableName As String, _
                                primaryFieldName As String, _
                                foreignTableName As String, _
                                foreignFieldName As String, [b]lngAttributes As Long[/b]) As Boolean
   On Error GoTo ErrHandler
    Dim blnEdit As Boolean
    Dim db As DAO.Database
    Dim newRelation As DAO.Relation
    Dim relatingField As DAO.Field
    Dim relationUniqueName As String
    
    relationUniqueName = primaryTableName + "_" + primaryFieldName + _
                         "__" + foreignTableName + "_" + foreignFieldName
    
    Set db = CurrentDb()
     If relationExists(relationUniqueName) Then
         blnEdit = MsgBox("Relation Already Exists. Do you want to edit?", vbYesNo)
         If blnEdit Then
           delRelation (relationUniqueName)
         End If
     End If
    'Arguments for CreateRelation(): any unique name,
    'primary table, related table, attributes.
    Set newRelation = db.CreateRelation(relationUniqueName, _
                            primaryTableName, foreignTableName)
    'The field from the primary table.
    Set relatingField = newRelation.CreateField(primaryFieldName)
    'Matching field from the related table.
    relatingField.ForeignName = foreignFieldName
    'Add the field to the relation's Fields collection.
    newRelation.Fields.Append relatingField
    'Add the relation to the database.
   [b]newRelation.Attributes = lngAttributes[/b]
    db.Relations.Append newRelation
    
    Set db = Nothing
    
    CreateRelation = True
    MsgBox "Relation " & relationUniqueName & " created."
Exit Function

ErrHandler:
    If Err.Number = 3012 Then
          
      
    Else
      Debug.Print Err.Description + " (" + relationUniqueName + ")"
    End If
    CreateRelation = False
End Function

Public Sub delRelations()
    Dim db As DAO.Database
    Dim totalRelations As Integer
    Dim i As Integer
    Set db = CurrentDb()
    totalRelations = db.Relations.Count
    If totalRelations > 0 Then
        For i = totalRelations - 1 To 0 Step -1
            db.Relations.Delete (db.Relations(i).Name)
        Next i
        Debug.Print Trim(Str(totalRelations)) + " Relationships deleted!"
    End If
End Sub

Public Sub delRelation(strRel As String)
  CurrentDb.Relations.Delete (strRel)
End Sub

Public Function relationExists(strRel As String) As Boolean
  Dim rel As Relation
  For Each rel In CurrentDb.Relations
    If rel.Name = strRel Then relationExists = True
  Next rel
End Function

Finding the attributes is a pain. I looked in the helpfile and it is not helpful.

They can be added like below.
dbRelationLeft Or dbRelationUpdateCascade Or dbRelationDeleteCascade