ADO - Sort

In ADO (ActiveX Data Objects), the Sort property is used to arrange (sort) the records in a Recordset in ascending or descending order based on one or more fields — similar to using an ORDER BY clause in SQL, but applied after the data is retrieved.

1. Purpose

The Sort property changes the order of records currently in the Recordset, without executing a new query on the database.

2. Syntax

rs.Sort = "FieldName [ASC|DESC]"

  • ASC = Ascending order (default)

  • DESC = Descending order

3. Example 1 – Simple Sort

rs.Open "SELECT * FROM Employees", conn, adOpenStatic, adLockReadOnly

' Sort employees by name in ascending order
rs.Sort = "EmployeeName ASC"

Do Until rs.EOF
    MsgBox rs("EmployeeName")
    rs.MoveNext
Loop

4. Example 2 – Sort in Descending Order

' Sort employees by salary in descending order
rs.Sort = "Salary DESC"

5. Example 3 – Sort by Multiple Fields

' Sort first by Department (A–Z), then by Salary (highest to lowest)
rs.Sort = "Department ASC, Salary DESC"

6. Key Points

  • Sort affects only the order of records — it does not change the data in the database.

  • The property works only if the Recordset supports sorting (typically with client-side cursors).

    rs.CursorLocation = adUseClient

  • You can use Sort together with Filter:

    rs.Filter = "Department = 'Sales'"
rs.Sort = "Salary DESC"

  • After sorting, you can still move through the records using MoveFirst, MoveNext, etc.

7. Related Notes

  • Equivalent SQL example:

    SELECT * FROM Employees ORDER BY Salary DESC

    However, Sort allows you to rearrange records without re-querying the database.

  • Sorting happens in memory, making it faster for small to medium Recordsets.

8. Example Summary

rs.Open "SELECT * FROM Employees", conn, adOpenStatic, adLockReadOnly
rs.Sort = "HireDate DESC"

MsgBox "First employee hired last: " & rs("EmployeeName")