Referential Integrity

[wellsedj]

Is it possible to programmatically, using VBA, to change referential integrity between tables in the same database?

Is it possible to programmatically, using VBA, to add and remove Cascade Update Related Fields or Cascade Delete Related Records in the relation ship between tables in the same database?

Or must the Relationships dialog box be used to accomplish the above?

 

[bayman]

Run SQL with an ALTER TABLE statement

 

[wellsedj]

Bayman,

Thank you for your reply. I think your reply is a little over my head. How do I setup the "Run SQL with an ALTER TABLE statement"?

The tables in question are tblSecurities, tblYears, tblJan1NAVs, tblCapGains, tblDividends, and tblDec31NAVs. There is an hierarchy relationship between tblSecurities, tblYears, and each of the other tables (tblJan1NAVs, tblCapGains, tblDividends, and tblDec31NAVs).

There is no relationship between tables tblJan1NAVs, tblCapGains, tblDividends, and tblDec31NAVs.

 

[bayman]

You can find examples in the Access help facility. Search for 'ALTER TABLE'.

 

[wellsedj]

Bayman,

I looked in the help files under Alter Table Statement. It covers adding a field to a table, changing an existing field's data type, adding a foreign key field, and removing a foreign key field.

What table property or method must be altered to add or remove referential integrity, add or remove cascading updates or deletes?

 

[Pat Hartman]

Foreign keys are constraints and are used to enforce referential integrity. The CreateRelation Method will create relationships for you. Look up the Attributes for the specific RI rules. Here are some examples:

Sub SubMakeRelations()
Dim Myrel As Relation
Dim MyDB As Database
Dim MyFld As Field

Set MyDB = DBEngine.Workspaces(0).Databases(0)

'Company -->> Company_Code
Set Myrel = MyDB.CreateRelation("RelComp_CompCode")
Myrel.Table = "dbo_EC_Company"
Myrel.ForeignTable = "dbo_EC_Company_Code"
Myrel.Attributes = 0
Set MyFld = Myrel.CreateField("Company_ID")
MyFld.ForeignName = "Company_ID"
Myrel.Fields.Append MyFld
MyDB.Relations.Append Myrel
End Sub

Sub SubDelRelations()
Dim Myrel As Relation
Dim MyDB As Database

Set MyDB = DBEngine.Workspaces(0).Databases(0)

'Company -->> Company_Code
MyDB.Relations.Delete "RelComp_CompCode"
End Sub

 

[wellsedj]

Pat,

Thank you for your reply. I forgot to mention in previous posts, that I am trying to do this in ADO. You supplied me with the method of doing what I wanted to do any way.

I got the following two subs to work for me:

To establish a relationship:
Private Sub cmdAddRelshp_Click()

Dim Rel As New ADOX.Key
Dim cat As New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

'If relationship already exists, then error -214767259 occurs

With Rel
'You just make up a name
.Name = "SecNameYears"
.Type = adKeyForeign
'Name of Table that is the "one" side of a one-to-many relationship
.RelatedTable = "tblSecurities"
'Name of "one" side primary key
.Columns.Append "strSecName"
'Name of "many" side foreign key and name of "one" side primary key
.Columns("strSecName").RelatedColumn = "strSecName"
'Set UpdateRule property to turn on cascading updates
.UpdateRule = adRICascade
'Set DeleteRule property to turn on cascading deletes
.DeleteRule = adRICascade
End With

'Name of Table that is the "many" side of a one-to-many relationship
cat.Tables("tblYears").Keys.Append Rel

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set Rel = Nothing

End Sub

Then to delete a relationship:
Private Sub cmdDelRelshp_Click()

Dim cat As New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

cat.Tables("tblYears").Keys.Delete "SecNameYears"

Set cat.ActiveConnection = Nothing
Set cat = Nothing

End Sub