Poppy,
The basic procedure for creating a Relationship in VBA is as follows:
1. Create the Relation object using the Database's CreateRelation method.
2. Set the Relation object's attributes as appropriate.
3. Create the fields that participate in the relationship, using the
Relation object's CreateField method.
4. Set the Field objects' attributes as appropriate.
5. Append each field to the Relation's Fields collection.
6. Append the Relation object to the Database's Relations collection.
The following code creates a relationship whose name is specified by
'the strRelName argument, specifies its attributes, and adds the tables
'and fields that make up the relationship. Note that you can name a
'relationship any way you like, but when you create a relationship using
'the Relationships window, Access names the relationship according to
'the names of the tables involved. For example, if you were to create a
'relationship between tblInvoice and tblInvItem, Access would name it
'tblInvoicetblInvItem.
Public Sub CreateRelation(strRelName As String, _
strSrcTable As String, strSrcField As String, _
strDestTable As String, strDestField As String)
Dim dbs As Database
Dim fld As DAO.Field
Dim rel As DAO.Relation
Dim varRel As Variant
Set dbs = CurrentDb
On Error Resume Next
'Check if the relationship already exists.
'If so, delete it.
If IsObject(dbs.Relations(strRelName)) Then
dbs.Relations.Delete strRelName
End If
'Create the relation object
Set rel = dbs.CreateRelation(strRelName, _
strSrcTable, _
strDestTable)
'The Relation object now exists in memory, but as with the TableDef
'and Index objects, it won't be a permanent part of the database until
'you append it to the Database's Relations collection. The following
'code segment defines the relationship's attributes. Notice that I have
'used three Relation attribute enum values; dbRelationLeft,
'dbRelationUpdateCascade and dbRelationDeleteCascade. These of
'course, define a LEFT JOIN relationship with referential integrity set
'to Cascade Update and Cascade Delete. When you specify the
'Attribute property, use the sum of the enum values you want to include.
'This is generally accomplished using the logical OR operator, rather
'than the unary plus (+).
'Set this relationship to:
' LEFT JOIN
' Referential integrity = Cascade Update and Cascade Delete
rel.Attributes = dbRelationLeft Or _
dbRelationUpdateCascade Or _
dbRelationDeleteCascade
'Once the Relation object has been created and its attributes specified,
'you then add all the fields that collectively form the relationship.
Lastly,
'you add the new relationship to the Database's Relations collection to
'make it permanent, and refresh it.
'Append the field(s) involved in the relationship
Set fld = rel.CreateField(strSrcField)
fld.ForeignName = strDestField
'Append the field to the relation's Fields collection
rel.Fields.Append fld
'Append the relation to the Database's Relations collection
dbs.Relations.Append rel
'Refresh the Relations collection
dbs.Relations.Refresh
Set rel = Nothing
Set fld = Nothing
Set dbs = Nothing
End Sub
When you create your own relationships in code, they will not automatically
'appear in the Relationships window. To display the Relationships window,
'display the Database window, then select Relationships from the Tools
'menu. To display the new relationships you've created in code, you either
'add the related tables to the Relationships window, or click Show All from
'the Relationships menu.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html