VB6 - How to sort data by multiple fields using an Unconnected Table(ADODB.Recordset)

[CVMichael]

This example shows how to sort a User Defined Type (UDT), but you can change it for use with any kind of data (not only UDTs).

This shows how to make a temporary unconnected table, where you insert data into, then you can sort by any field, and any combination, just like you do it in SQL.

You must add a reference to "Microsoft ActiveX Data Objects x.x Library" where "x.x" is your latest version that you have.

Option Explicit
 
Private Type t_MyData
    strField As String
    intField As Long
    dateField As Date
End Type
 
Private ArrMyData(0 To 30) As t_MyData
 
Private Sub Form_Load()
    Dim rsData As New ADODB.Recordset ' unconnected table (Add a reference to "Microsoft ActiveX Data Objects")
    Dim K As Long
    
    Randomize
    
    ' put some random data into the array for testing
    For K = LBound(ArrMyData) To UBound(ArrMyData)
        ArrMyData(K).strField = Chr(65 + 3 * Rnd)
        ArrMyData(K).intField = Fix(100 * Rnd)
        ArrMyData(K).dateField = DateAdd("d", -K, Date)
    Next K
    
    With rsData
        ' create the fields that we are gonna sort
        .Fields.Append "ArrIndex", adInteger
        .Fields.Append "strField", adVarChar, 255
        .Fields.Append "intField", adInteger
        .Fields.Append "dateField", adDate
        
        .Open ' open the recordset so we can add our data
        
        ' add the data into the temporary unconnected table
        For K = LBound(ArrMyData) To UBound(ArrMyData)
            .AddNew
            
            .Fields("ArrIndex").Value = K
            .Fields("strField").Value = ArrMyData(K).strField
            .Fields("intField").Value = ArrMyData(K).intField
            .Fields("dateField").Value = ArrMyData(K).dateField
            
            .Update
        Next K
        
        ' do the sorting
        .Sort = "[strField] DESC, [intField] ASC"
        
        ' display the result in the Immediate window (debug window)
        .MoveFirst
        Do Until rsData.BOF Or rsData.EOF
            Debug.Print .Fields("ArrIndex").Value, .Fields("strField").Value, .Fields("intField").Value, .Fields("dateField").Value
            
            .MoveNext
        Loop
    End With
End Sub