Table Sort Order VBA

[D Willett]

Hi Guys

With the following code, is it possible to apply a sort order ASC or DESC based on the JobID column ( Header name is JobID ) ?:

Code: Select all

Sub RunCycleQuery()
 
    'Step 1: Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
    'ProgressBar.Show
     
    'Step 2: Identify the database and query
    Set MyDatabase = DBEngine.OpenDatabase _
    (ActiveWorkbook.Path & "\Autoflow-Dash.mdb")
    Set MyQueryDef = MyDatabase.QueryDefs("qryK2K")
     
    'Step 4: Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset
     
    'Step 5: Clear previous contents
    Range("Cycle!A1:CC10000").ClearContents
     
    'Step 6: Copy the recordset to Excel
    Range("Cycle!A2").CopyFromRecordset MyRecordset
     
    'Step 7: Add column heading names to the spreadsheet
    For i = 1 To MyRecordset.Fields.Count
    Sheets("Cycle").Cells(, i).Value = MyRecordset.Fields(i - 1).Name
    
    Next i
    
        Sheets("Cycle").ListObjects.Add SourceType:=xlSrcRange, _
        Source:=Sheets("Cycle").Range("A1").CurrentRegion, _
        XLListObjectHasHeaders:=xlYes
End Sub

[HansV]

Replace the lines

Code: Select all

    Set MyQueryDef = MyDatabase.QueryDefs("qryK2K")
     
    'Step 4: Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset

with

Code: Select all

    Set MyRecordset = MyDatabase.OpenRecordset("SELECT * FROM qryK2K ORDER BY JobID")

or

Code: Select all

    Set MyRecordset = MyDatabase.OpenRecordset("SELECT * FROM qryK2K ORDER BY JobID DESC")

depending on the desired sort order.

[D Willett]

Works like a charm. Thank You Hans