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