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