ForeignName Property

Applies To Field object.

Description

Sets or returns a value that specifies the name of the Field object in a foreign table that corresponds to a field in a primary table for a relationship (Microsoft Jet workspaces only).

Settings and Return Values

The setting or return value is a String data type that evaluates to the name of a Field in the associated TableDef object's Fields collection.

If the Relation object isn't appended to the Database, but the Field is appended to the Relation object, the ForeignName property is read/write. Once the Relation object is appended to the database, the ForeignName property is read-only.

Remarks

Only a Field object that belongs to the Fields collection of a Relation object can support the ForeignName property.

The Name and ForeignName property settings for a Field object specify the names of the corresponding fields in the primary and foreign tables of a relationship. The Table and ForeignTable property settings for a Relation object determine the primary and foreign tables of a relationship.

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 exist 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 foreign table, so the ForeignTable property of the Relation object would be set to ValidParts and the Table 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.

See Also ForeignTable property, Name property, Table property.

Example

This example shows how the Table, ForeignTable, and ForeignName properties define the terms of a Relation between two tables.

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

Example (Microsoft Access)

The following example shows how you can use the ForeignName, ForeignTable, and Table properties when you create a relationship between two existing tables — in this case, Employees (the primary table) and Orders (the foreign table) in the current database. EmployeeID is the primary key in the Employees table, and also a foreign key in the Orders table. The relationship is one-to-many and referential integrity is enforced.

Note that the And operator performs a bitwise comparison to determine what permissions are currently set.

Sub NewRelation()
    Dim dbs As Database
    Dim fld As Field, rel As Relation

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Find existing EmployeesOrders relation.
    For Each rel In dbs.Relations
        If rel.Table = "Employees" _
                And rel.ForeignTable = "Orders" Then
            ' Prompt user before deleting relation.
            If MsgBox(rel.Name & " already exists. " & vbCrLf _
                    & "This relation will be deleted and re-created.", _
                    vbOK) = vbOK Then
                dbs.Relations.Delete rel.Name
            ' If user chooses Cancel, exit procedure.
            Else
                Exit Sub
            End If
        End If
    Next rel
    ' Create new relationship and set its properties.
    Set rel = dbs.CreateRelation("EmployeesOrders")
    ' Set Table property.
    rel.Table = "Employees"
    ' Set ForeignTable property.
    rel.ForeignTable = "Orders"
    ' Set Relation object attributes to enforce referential integrity.
    rel.Attributes = dbRelationDeleteCascade And dbRelationUpdateCascade
    ' Create field in Fields collection of Relation.
    Set fld = rel.CreateField("EmployeeID")
    ' Provide name of foreign key field.
    fld.ForeignName = "EmployeeID"
    ' Append field to Relation and Relation to database.
    rel.Fields.Append fld
    dbs.Relations.Append rel
    MsgBox "Relation '" & rel.Name & "' created."
    Set dbs = Nothing
End Sub