How to create a relationship in VBA

[Tran Hong Quang]

Hi,
Could someone give me a sample in VBA to create one-many
relationship between 2 tables?

Thks
Quang

 

[Allen Browne]

Sub CreateRelationDAO()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

'Initialize
Set db = CurrentDb()

'Create a new relation.
Set rel = db.CreateRelation("tblDaoContractortblDaoBooking")

'Define its properties.
With rel
'Specify the primary table.
.Table = "tblDaoContractor"
'Specify the related table.
.ForeignTable = "tblDaoBooking"
'Specify attributes for cascading updates and deletes.
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade

'Add the fields to the relation.
'Field name in primary table.
Set fld = .CreateField("ContractorID")
'Field name in related table.
fld.ForeignName = "ContractorID"
'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.
db.Relations.Append rel

'Clean up
Set fld = Nothing
Set rel = Nothing
Set db = Nothing
Debug.Print "Relation created."
End Sub

 

[Tim Ferguson]

Could someone give me a sample in VBA to create one-many
relationship between 2 tables?

' check the help files for the CONSTRAINT clause for details
strSQL = "ALTER TABLE MyTable" & vbCrLf & _
"ADD CONSTRAINT MyFK FOREIGN KEY" & vbCrLf & _
" FKField REFERENCES OtherTable (PKField)"

' you can use a Connection object instead if you want to
' use ADO rather than DAO
db.Execute strSQL, dbFailOnError

Hope that helps


Tim F