Sorting a Table

You can sort a database by using the Order By SQL statement with or without indexes. 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, and displays the data in 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