ForeignName Property

Applies To

Field Object.

Description

Sets or returns a value that, in a relationship, specifies the name of the Field object in a foreign table that corresponds to a field in a primary table.

Settings and Return Values

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

Remarks

Only a Field object that belongs to a Fields collection contained by a Relation object can support the ForeignName property. If the Relation isn't appended to the Database, but the Field is appended to the Relation, you can still change the ForeignName property. Once the Relation object is appended to the database, the properties of the Field object are read-only.

The Name and ForeignName property value 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 value argument settings of 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 was entered into the OrderItem table it must exist in the ValidParts table. If the part code did not exist in the ValidParts table and you had not indicated no enforcement of referential integrity in the Attributes property, a trappable error would occur.

In this case, the ValidParts table would be an example of a Primary table, so the ForeignTable property of the Relation object would be set to OrderItem and the Table property of the Relation object would be set to ValidParts. The Name property and ForeignName property of the Field object would both be PartNo.

See Also

ForeignTable Property, Name Property, Table Property.

Example

This example shows how you can use the ForeignName, ForeignTable, and Table properties when you create a relationship between two existing tables — in this case, Table1 (the primary table) and Table2 (the foreign table) in the specified database. Field1 is the primary key in Table1, and Field2 is a foreign key in Table2. The relationship is one-to-many and referential integrity is enforced.

Function ForeignNameTable () As Integer
    Dim dbsDefault As Database
    Dim fldLocal As Field, relForeign As Relation
' Get database.
    Set dbsDefault = _
         DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
' Create new relationship and set its properties.
    Set relForeign = dbsDefault.CreateRelation("MyRelation") 
    relForeign.Table = "Table1"
    relForeign.ForeignTable = "Table2"
' Create field and set its properties.
    Set fldLocal = relForeign.CreateField("Field1")
    fldLocal.ForeignName = "Field2"
' Append field to relation and relation to database.
    relForeign.Fields.Append fldLocal
    dbsDefault.Relations.Append relForeign
    dbsDefault.Close
End Function

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.

To test the following example in Microsoft Access, open the Northwind database and click Relationships on the Tools menu. Delete the relationship between the Employees table and the Orders table. Close the Relationships window, saving the current configuration when prompted. Then run the following procedure from a standard module, and view the Relationships window again to see the new relationship.

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

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    ' Create new relationship and set its properties.
    Set rel = dbs.CreateRelation("EmployeesRelation")
    ' Denote primary table.
    rel.Table = "Employees"

    ' Denote foreign table.
    rel.ForeignTable = "Orders"
    rel.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
    ' 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
End Sub