Sort Property Example (VB)

This example uses the Recordset object's Sort property to reorder the rows of a Recordset derived from the Authors table of the Pubs database. A secondary utility routine prints each row.

'BeginSortVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub SortX()
    
     ' connection and recordset variables
    Dim Cnxn As New ADODB.Connection
    Dim rstAuthors As New ADODB.Recordset
    Dim strCnxn As String
    Dim strSQLAuthors As String
        
    Dim strTitle As String
        
   ' Open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
    
    ' open client-side recordset to enable sort method
    Set rstAuthors = New ADODB.Recordset
    rstAuthors.CursorLocation = adUseClient
    strSQLAuthors = "SELECT * FROM Authors"
    rstAuthors.Open strSQLAuthors, Cnxn, adOpenStatic, adLockReadOnly, adCmdText
    
     ' sort the recordset last name ascending
    rstAuthors.Sort = "au_lname ASC, au_fname ASC"
     ' show output
    Debug.Print "Last Name Ascending:"
    Debug.Print "First Name  Last Name" & vbCr
    
    rstAuthors.MoveFirst
    Do Until rstAuthors.EOF
       Debug.Print rstAuthors!au_fname & " " & rstAuthors!au_lname
       rstAuthors.MoveNext
    Loop
    
     ' sort the recordset last name descending
    rstAuthors.Sort = "au_lname DESC, au_fname ASC"
     ' show output
    Debug.Print "Last Name Descending"
    Debug.Print "First Name  Last Name" & vbCr
    
    Do Until rstAuthors.EOF
       Debug.Print rstAuthors!au_fname & " " & rstAuthors!au_lname
       rstAuthors.MoveNext
    Loop
     
     'clean up
    rstAuthors.Close
    Cnxn.Close
    Set rstAuthors = Nothing
    Set Cnxn = Nothing
    
End Sub
'EndSortVB

This is the secondary utility routine that prints the given title, and the contents of the specified Recordset.

Attribute VB_Name = "Sort"

See Also

Recordset Object | Sort Property

© 1998-2001 Microsoft Corporation. All rights reserved.