Sorting a Table

You can sort a database by using the Order By statement with or without indexes. However, sorting a database without an index is generally slower than sorting a database with an index. The following code example shows how to sort a database without an index. It requires a ListBox named List1.

Dim rs
Set rs = CreateObject("ADOCE.RecordSet")
rs.open "select * from mytable order by firstfield desc", "", 1, 3
If rs.RecordCount > 0 Then
  Do While Not rs.EOF
    List1.Additem rs.Fields("firstfield").Value
    rs.MoveNext
  Loop
End If
rs.Close
set rs = Nothing

The following code example shows how to create an index on a database that is used for faster sorting. It requires a ListBox named List1.

Dim rs
Set rs = CreateObject("ADOCE.RecordSet")
rs.open "create index i1 on mytable (firstfield desc)"
rs.open "select * from mytable order by firstfield desc", "", 1, 3
If rs.RecordCount > 0 Then
  Do While Not rs.EOF
    List1.Additem rs.Fields("firstfield").Value
    rs.MoveNext
  Loop
End If
rs.Close
set rs = Nothing