Reorder records in Access table from within excel listbox vba

[lpking2005]

Hi, All

I have a userform listbox which pulls some entries from an Access table.
What i need to be able to do is use a spinbutton to select an entry and be able to reorder the records and then have this saved to the actual database.

The database entries are sorted by a column called [Order Priority], which is assigned from 1-5.

Here is my code for pulling the database entries. Its probably messy, but it works.

Any ideas or help would be appreciated. Thank you!

Dim cnt As ADODB.Connection, _
    rst As ADODB.Recordset, _
 _
    stCon As String, _
    stDB As String, _
    sSql As String, _
 _
    sRow As Integer, _
    sCol As Integer, _
    L1 As Integer, _
    L2 As Integer, _
    X As Integer, _
    Y As Integer, _
    i As Integer, _
 _
    rng As Range, _
    c As Range, _
 _
    ws As Worksheet, _
    Rew As String


      




On Error GoTo eHandler




'Initiate the ADO COM objects
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
 
 
'Path and name of the database
    stDB = GetDriveLetter & DATA_FLDR & MLD_FLAG
    'stDB = "C" & DATA_FLDR & MLD_FLAG '******************************* override
    
'set string for rework
    Rew = "Rework"


'Create the connection string.
stCon = "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;" & _
                   "Data Source=" & stDB & ";"


'Open the connection
cnt.Open stCon




   'From 401 to 612
    i = WhichLine


        
        'Finds the range of each lines order cells. eg.401("B9:D19")
        On Error Resume Next
            Set rng = Nothing
            If i = "613" Then
                Set rng = ThisWorkbook.ActiveSheet.Range("Orders_" & Rew)
            Else
                Set rng = ThisWorkbook.ActiveSheet.Range("Orders_" & i)
            End If
        On Error GoTo 0
        
        
        If Not rng Is Nothing Then
   
            
            'Open the table and sort by Order Priority
            rst.CursorLocation = adUseClient
            rst.Sort = "[OrderPriority]"
            
            'Select jobs from DB which have not been confirmed to be completed
            If i = "613" Then
                sSql = "SELECT * FROM " & Rew & " WHERE [TL Confirm] = FALSE" ' ORDER BY [OrderPriority] ASC"
            Else
                sSql = "SELECT * FROM " & i & " WHERE [TL Confirm] = FALSE" ' ORDER BY [OrderPriority] ASC"
            End If
            
            rst.Open sSql, cnt, 1, 3
            
            
            
            If Not (rst.BOF Or rst.EOF) Then
                    
                    rst.MoveFirst
                    
                    i = 0
                    
                    With frm_EditOrderPriority.ListBox1
                        .Clear
                        Do
                            .AddItem
                            .List(i, 0) = rst![Order Number]
                            .List(i, 1) = rst![TO Number]
                            .List(i, 2) = rst![Quantity]
                            i = i + 1
                            rst.MoveNext
                        Loop Until rst.EOF
                    End With
            
            End If
            
            rst.Close
            
        End If
 
cnt.Close
    
eHandler:


'Close the connections
On Error Resume Next


    'Tidy up by releasing the variables
    Set cnt = Nothing
    Set rst = Nothing


On Error GoTo 0