CreateField Method

Applies To Index object, Relation object, TableDef object.

Description

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

Syntax

Set field = object.CreateField(name, type, size)

The CreateField method syntax has these parts.

Part	Description

field	An object variable that represents the Field object you want to create.
object	An object variable that represents the Index, Relation, or TableDef object for which you want to create the new Field object.
name	Optional. A Variant (String subtype) that uniquely names the new Field object. See the Name property for details on valid Field names.
type	Optional. A constant that determines the data type of the new Field object. See the Type property for valid data types.
size	Optional. A Variant (Integer subtype) that indicates the maximum size, in bytes, of a Field object that contains text. See the Size property for valid size values. This argument is ignored for numeric and fixed-width fields.

Remarks 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.

See Also ALTER TABLE statement, Append method, CREATE TABLE statement, Delete method, Field object, Name property, Size property, Type property.

Example

This example uses the CreateField method to create three Fields for a new TableDef. It then displays the properties of those Field objects that are automatically set by the CreateField method. (Properties whose values are empty at the time of Field creation are not shown.)

Sub CreateFieldX()

    Dim dbsNorthwind As Database
    Dim tdfNew As TableDef
    Dim fldLoop As Field
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")

    ' Create and append new Field objects for the new
    ' TableDef object.
    With tdfNew
        ' The CreateField method will set a default Size
        ' for a new Field object if one is not specified.
        .Fields.Append .CreateField("TextField", dbText)
        .Fields.Append .CreateField("IntegerField", dbInteger)
        .Fields.Append .CreateField("DateField", dbDate)
    End With

    dbsNorthwind.TableDefs.Append tdfNew

    Debug.Print "Properties of new Fields in " & tdfNew.Name

    ' Enumerate Fields collection to show the properties of
    ' the new Field objects.
    For Each fldLoop In tdfNew.Fields
        Debug.Print "    " & fldLoop.Name

        For Each prpLoop In fldLoop.Properties
            ' Properties that are invalid in the context of
            ' TableDefs will trigger an error if an attempt
            ' is made to read their values.
            On Error Resume Next
            Debug.Print "        " & prpLoop.Name & " - " & _
                IIf(prpLoop = "", "[empty]", prpLoop)
            On Error GoTo 0
        Next prpLoop

    Next fldLoop

    ' Delete new TableDef because this is a demonstration.
    dbsNorthwind.TableDefs.Delete tdfNew.Name
    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example creates a new table with two new fields. One of the fields is an AutoNumber field. The procedure also makes this field the primary key in the table.

Sub NewTable()
    Dim dbs As Database
    Dim tdf As TableDef, fld1 As Field, fld2 As Field
    Dim idx As Index, fldIndex As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create new table with two fields.
    Set tdf = dbs.CreateTableDef("Contacts")
    Set fld1 = tdf.CreateField("ContactID", dbLong)
    fld1.Attributes = fld1.Attributes + dbAutoIncrField
    Set fld2 = tdf.CreateField("ContactName", dbText, 50)
    ' Append fields.
    tdf.Fields.Append fld1
    tdf.Fields.Append fld2
    ' Create primary key index.
    Set idx = tdf.CreateIndex("PrimaryKey")
    Set fldIndex = idx.CreateField("ContactID", dbLong)
    ' Append index fields.
    idx.Fields.Append fldIndex
    ' Set Primary property.
    idx.Primary = True
    ' Append index.
    tdf.Indexes.Append idx
    ' Append TableDef object.
    dbs.TableDefs.Append tdf
    dbs.TableDefs.Refresh
    Set dbs = Nothing
End Sub