この例では、Index の Columns コレクションに追加された Column の SortOrder プロパティの機能を示します。コードでは、昇順のインデックスを Employees テーブルの Country 列に追加し、レコードを表示します。次に、降順のインデックスを Employees テーブルの Country 列に追加し、レコードを再び表示します。昇順インデックスと降順インデックスの違いが示されます。
Sub SortOrderX()
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"
'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"
' Append descending index to Employees table
catNorthwind.Tables("Employees").Indexes.Append idxDescending
rstEmployees.Index = idxDescending.Name
rstEmployees.Open "Employees", cnn, adOpenKeyset, _
adLockOptimistic, adCmdTableDirect
' DisplaySortedRecords (rstEmployees)
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
Set catNorthwind = Nothing
End Sub