OrdinalPosition Property Example

This example changes the OrdinalPosition property values in the Employees TableDef in order to control the Field order in a resulting Recordset. By setting the OrdinalPosition of all the Fields to 1, any resulting Recordset will order the Fields alphabetically. Note that the OrdinalPosition values in the Recordset don't match the values in the TableDef, but simply reflect the end result of the TableDef changes.

Sub OrdinalPositionX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim aintPosition() As Integer
    Dim astrFieldName() As String
    Dim intTemp As Integer
    Dim fldTemp As Field
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind.TableDefs("Employees")

    With tdfEmployees
        ' Display and store original OrdinalPosition data.
        Debug.Print _
            "Original OrdinalPosition data in TableDef."
        ReDim aintPosition(0 To .Fields.Count - 1) As Integer
        ReDim astrFieldName(0 To .Fields.Count - 1) As String
        For intTemp = 0 To .Fields.Count - 1
            aintPosition(intTemp) = _
                .Fields(intTemp).OrdinalPosition
            astrFieldName(intTemp) = .Fields(intTemp).Name
            Debug.Print , aintPosition(intTemp), _
                astrFieldName(intTemp)
        Next intTemp

        ' Change OrdinalPosition data.
        For Each fldTemp In .Fields
            fldTemp.OrdinalPosition = 1
        Next fldTemp

        ' Open new Recordset object to show how the 
        ' OrdinalPosition data has affected the record order.
        Debug.Print _
            "OrdinalPosition data from resulting Recordset."
        Set rstEmployees = dbsNorthwind.OpenRecordset( _
            "SELECT * FROM Employees")
        For Each fldTemp In rstEmployees.Fields
            Debug.Print , fldTemp.OrdinalPosition, fldTemp.Name
        Next fldTemp
        rstEmployees.Close

        ' Restore original OrdinalPosition data because this is
        ' a demonstration.
        For intTemp = 0 To .Fields.Count - 1
            .Fields(astrFieldName(intTemp)).OrdinalPosition = _
                aintPosition(intTemp)
        Next intTemp

    End With

    dbsNorthwind.Close

End Sub