SortOrder Property Example (VB)

This example demonstrates the SortOrder property of a Column that has been appended to the Columns collection of an Index. The code appends an ascending index to the Country column in the Employees table, then displays the records. Then the code appends a descending index to the Country column in the Employees table and displays the records again. The difference between ascending and descending indexes is shown.

' BeginSortOrderVB
Sub Main()
    On Error GoTo SortOrderXError

    Dim cnn As New ADODB.Connection
    Dim catNorthwind As New ADOX.Catalog
    Dim idxAscending As New ADOX.Index
    Dim idxDescending As New ADOX.Index
    Dim rstEmployees As New ADODB.Recordset
        
    ' Connect the catalog.
    cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source='c:\Program Files\" & _
        "Microsoft Office\Office\Samples\Northwind.mdb';"
    Set catNorthwind.ActiveConnection = cnn

    ' Append Country column to new index
    idxAscending.Columns.Append "Country"
    idxAscending.Columns("Country").SortOrder = adSortAscending
    idxAscending.Name = "Ascending"
    idxAscending.IndexNulls = adIndexNullsAllow
    
    'Append new index to Employees table
    catNorthwind.Tables("Employees").Indexes.Append idxAscending
    
    rstEmployees.Index = idxAscending.Name
    rstEmployees.Open "Employees", cnn, adOpenKeyset, _
        adLockOptimistic, adCmdTableDirect
        
    With rstEmployees
        .MoveFirst
        Debug.Print "Index = " & .Index
        Debug.Print "  Country - Name"

        ' Enumerate the Recordset. The value of the
        ' IndexNulls property will determine if the newly
        ' added record appears in the output.
        Do While Not .EOF
            Debug.Print "    " & !Country & " - " & _
                !FirstName & " " & !LastName
            .MoveNext
        Loop

        .Close
    End With

    ' Append Country column to new index
    idxDescending.Columns.Append "Country"
    idxDescending.Columns("Country").SortOrder = adSortDescending
    idxDescending.Name = "Descending"
    idxDescending.IndexNulls = adIndexNullsAllow
    
    'Append descending index to Employees table
    catNorthwind.Tables("Employees").Indexes.Append idxDescending
    
    rstEmployees.Index = idxDescending.Name
    rstEmployees.Open "Employees", cnn, adOpenKeyset, _
        adLockOptimistic, adCmdTableDirect
                
    With rstEmployees
        .MoveFirst
        Debug.Print "Index = " & .Index
        Debug.Print "  Country - Name"
        
        ' Enumerate the Recordset. The value of the
        ' IndexNulls property will determine if the newly
        ' added record appears in the output.
        Do While Not .EOF
            Debug.Print "    " & !Country & " - " & _
                !FirstName & " " & !LastName
            .MoveNext
        Loop
        
        .Close
    End With
       
    ' Delete new Indexes because this is a demonstration.
    catNorthwind.Tables("Employees").Indexes.Delete idxAscending.Name
    catNorthwind.Tables("Employees").Indexes.Delete idxDescending.Name
    
    'Clean up
    cnn.Close
    Set catNorthwind = Nothing
    Set idxAscending = Nothing
    Set idxDescending = Nothing
    Set rstEmployees = Nothing
    Set cnn = Nothing
    Exit Sub
    
SortOrderXError:
    
    Set catNorthwind = Nothing
    Set idxAscending = Nothing
    Set idxDescending = Nothing

    If Not rstEmployees Is Nothing Then
        If rstEmployees.State = adStateOpen Then rstEmployees.Close
    End If
    Set rstEmployees = Nothing
    
    If Not cnn Is Nothing Then
        If cnn.State = adStateOpen Then cnn.Close
    End If
    Set cnn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
' EndSortOrderVB

See Also

Column Object | Columns Collection | Index Object | SortOrder Property