Class Relation (DAO VBA)

A Relation object represents a relationship between fields in tables or queries (Microsoft Access database engine databases only). To use a Relation class variable it first needs to be instantiated, for example

Dim rel as Relation
Set rel = Workspaces(1).Databases(1).Relations(Item:=1)

Attributes

Sets or returns a value that indicates one or more characteristics of a Relation object.

For an object not yet appended to a collection, this property is read/write.

Sub AttributesX() 
 
 Dim dbsNorthwind As Database 
 Dim fldLoop As Field 
 Dim relLoop As Relation 
 Dim tdfloop As TableDef 
 
 Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
 
 With dbsNorthwind 
 
 ' Display the attributes of a TableDef object's 
 ' fields. 
 Debug.Print "Attributes of fields in " & _ 
 .TableDefs(0).Name & " table:" 
 For Each fldLoop In .TableDefs(0).Fields 
 Debug.Print " " & fldLoop.Name & " = " & _ 
 fldLoop.Attributes 
 Next fldLoop 
 
 ' Display the attributes of the Northwind database's 
 ' relations. 
 Debug.Print "Attributes of relations in " & _ 
 .Name & ":" 
 For Each relLoop In .Relations 
 Debug.Print " " & relLoop.Name & " = " & _ 
 relLoop.Attributes 
 Next relLoop 
 
 ' Display the attributes of the Northwind database's 
 ' tables. 
 Debug.Print "Attributes of tables in " & .Name & ":" 
 For Each tdfloop In .TableDefs 
 Debug.Print " " & tdfloop.Name & " = " & _ 
 tdfloop.Attributes 
 Next tdfloop 
 
 .Close 
 End With 
 
End Sub 
 

CreateField

Creates a new Field object (Microsoft Access workspaces only).

You can use the CreateField method to create a new field, as well as specify the name, data type, and size of the field. If you omit one or more of the optional parts when you use CreateField, you can use an appropriate assignment statement to set or reset the corresponding property before you append the new object to a collection. After you append the new object, you can alter some but not all of its property settings. See the individual property topics for more details. The type and size arguments apply only to Field objects in a TableDef object. These arguments are ignored when a Field object is associated with an Index or Relation object. If name refers to an object that is already a member of the collection, a run-time error occurs when you use the Append method. To remove a Field object from a Fields collection, use the Delete method on the collection. You can't delete a Field object from a TableDef object's Fields collection after you create an index that references the field.

CreateField (Name, Type, Size)

Dim rel As DAO.Relation: Set rel = 
Dim fldCreateField As DAO.Field
Set fldCreateField = rel.CreateField

Arguments

Optional arguments

The following arguments are optional

Name (String) - A String that uniquely names the new Field object. See the Name property for details on valid Field names.

Type - Argument not supported for this object

Size (Long) - Argument not supported for this object.

Fields

Returns a Fields collection that represents all stored Field objects for the specified object.

Dim rel As DAO.Relation: Set rel = 
rel.Fields

ForeignTable

Sets or returns the name of the foreign table in a relationship (Microsoft Access workspaces only).

This property is read/write for a new Relation object not yet appended to a collection and read-only for an existing Relation object in the Relations collection. The ForeignTable property setting of a Relation object is the Name property setting of the TableDef or QueryDef object that represents the foreign table or query; the Table property setting is the Name property setting of the TableDef or QueryDef object that represents the primary table or query. For example, if you had a list of valid part codes (in a field named PartNo) stored in a ValidParts table, you could establish a relationship with an OrderItem table such that if a part code were entered into the OrderItem table, it would have to already be in the ValidParts table. If the part code didn't exist in the ValidParts table and you had not set the Attributes property of the Relation object to dbRelationDontEnforce, a trappable error would occur. In this case, the ValidParts table is the primary table, so the Table property of the Relation object would be set to ValidParts and the ForeignTable property of the Relation object would be set to OrderItem. The Name and ForeignName properties of the Field object in the Relation object's Fields collection would be set to PartNo.

    Sub ForeignNameX() 
     
     Dim dbsNorthwind As Database 
     Dim relLoop As Relation 
     
     Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
     
     Debug.Print "Relation" 
     Debug.Print " Table - Field" 
     Debug.Print " Primary (One) "; 
     Debug.Print ".Table - .Fields(0).Name" 
     Debug.Print " Foreign (Many) "; 
     Debug.Print ".ForeignTable - .Fields(0).ForeignName" 
     
     ' Enumerate the Relations collection of the Northwind 
     ' database to report on the property values of 
     ' the Relation objects and their Field objects. 
     For Each relLoop In dbsNorthwind.Relations 
     With relLoop 
     Debug.Print 
     Debug.Print .Name & " Relation" 
     Debug.Print " Table - Field" 
     Debug.Print " Primary (One) "; 
     Debug.Print .Table & " - " & .Fields(0).Name 
     Debug.Print " Foreign (Many) "; 
     Debug.Print .ForeignTable & " - " & _ 
     .Fields(0).ForeignName 
     End With 
     Next relLoop 
     
     dbsNorthwind.Close 
     
    End Sub

Name

Returns or sets the name of the specified object. Read/write String if the object has not been appended to a collection.

The maximum length for the name of a Relation object is 64 characters.

Dim rel As DAO.Relation: Set rel = 
rel.Name =

PartialReplica

Sets or returns a value on a Relation object indicating whether that relation should be considered when populating a partial replica from a full replica. (Microsoft Access database engine databases only).

The setting or return value is a Boolean data type that is True when the relation should be enforced during synchronization. This property enables you to replicate data from the full replica to the partial replica based on relationships between tables. You can use the PartialReplica property when setting the ReplicaFilter property alone can't adequately specify what data should be replicated to the partial. For example, suppose you have a database in which the Customers table has a one-to-many relationship with the Orders table, and you want to configure a partial replica that only replicates orders from customers in the California region (instead of all orders). It is not possible to set the ReplicaFilter property on the Orders table to Region = 'CA' because the Region field is in the Customers table, not the Orders table. To replicate all orders from the California region, you must indicate that the relation between the Orders and Customers tables will be active during replication. Once you've created a partial replica, the following steps will populate it with all orders from the California region:

Dim rel As DAO.Relation: Set rel = 
rel.PartialReplica = True

Properties

Returns the Properties collection of the specified object.

Dim rel As DAO.Relation: Set rel = 
rel.Properties

Table

Indicates the name of a Relation object's primary table. This should be equal to the Name property setting of a TableDef or QueryDef object (Microsoft Access workspaces only).

The Table property setting is read/write for a new Relation object not yet appended to a collection and read-only for an existing Relation object in a Relations collection. Use the Table property with the ForeignTable property to define a Relation object, which represents the relationship between fields in two tables or queries. Set the Table property to the Name property setting of the primary TableDef or QueryDef object, and set the ForeignTable property to the Name property setting of the foreign (referencing) TableDef or QueryDef object. The Attributes property determines the type of relationship between the two objects. For example, if you had a list of valid part codes (in a field named PartNo) stored in a ValidParts table, you could establish a one-to-many relationship with an OrderItem table such that if a part code were entered into the OrderItem table, it would have to already be in the ValidParts table. If the part code didn't exist in the ValidParts table and you had not set the Attributes property of the Relation object to dbRelationDontEnforce, a trappable error would occur. In this case, the ValidParts table is the primary table, so the Table property of the Relation object would be set to ValidParts and the ForeignTable property of the Relation object would be set to OrderItem. The Name and ForeignName properties of the Field object in the Relation object's Fields collection would be set to PartNo.

    Sub ForeignNameX() 
     
     Dim dbsNorthwind As Database 
     Dim relLoop As Relation 
     
     Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
     
     Debug.Print "Relation" 
     Debug.Print " Table - Field" 
     Debug.Print " Primary (One) "; 
     Debug.Print ".Table - .Fields(0).Name" 
     Debug.Print " Foreign (Many) "; 
     Debug.Print ".ForeignTable - .Fields(0).ForeignName" 
     
     ' Enumerate the Relations collection of the Northwind 
     ' database to report on the property values of 
     ' the Relation objects and their Field objects. 
     For Each relLoop In dbsNorthwind.Relations 
     With relLoop 
     Debug.Print 
     Debug.Print .Name & " Relation" 
     Debug.Print " Table - Field" 
     Debug.Print " Primary (One) "; 
     Debug.Print .Table & " - " & .Fields(0).Name 
     Debug.Print " Foreign (Many) "; 
     Debug.Print .ForeignTable & " - " & _ 
     .Fields(0).ForeignName 
     End With 
     Next relLoop 
     
     dbsNorthwind.Close 
     
    End Sub