OrdinalPosition Property Example (MDB)

The following example changes the setting of the OrdinalPosition property for the first field in a Products table. If you examine the table in Datasheet view before and after running this example, you'll see that it moves the ProductID field from the first column to the last column.

Sub SetPosition()
    Dim dbs As Database, tdf As TableDef
    Dim fldFirst As Field, fld As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Products table.
    Set tdf = dbs.TableDefs!Products
    ' Return reference to first field in table.
    Set fldFirst = tdf.Fields(0)
    ' Set OrdinalPosition property to last position in collection.
    fldFirst.OrdinalPosition = tdf.Fields.Count
    ' Refresh Fields collection.
    tdf.Fields.Refresh
    ' Enumerate all fields and print ordinal position.
    For Each fld In tdf.Fields
        Debug.Print fld.Name, fld.OrdinalPosition
    Next fld
    Set dbs = Nothing
End Sub