Export Excel Data to Access Table or Query using Excel VBA

[craigb1155]

I am trying to update records in access from a excel sheet.
I am able to add records to access using the below code i found, however when editing the code from rs.AddNew to rs.Edit it will only edit records if both are the first record.
I attempted to point to a query but was having no joy and suspect it something as simple as me not running the query correctly.

Hoping someone can spot where im going wrong.

PRODUCT_WO is our primary key and is unique ie to each record. The record contains both letters and numbers.

'Declaring the necessary variables.
    Dim accessFile  As String
    Dim accessTable As String
    Dim sht         As Worksheet
    Dim lastRow     As Long
    Dim lastColumn  As Integer
    Dim con         As Object
    Dim rs          As Object
    Dim Sql         As String
    Dim i           As Long
    Dim j           As Integer
            
    'Disable the screen flickering.
    Application.ScreenUpdating = False
    
    'Specify the file path of the accdb file.
    accessFile = "N:\Progress_BackEnd.accdb"
         
    'Ensure that the Access file exists.
    If FileExists(accessFile) = False Then
        MsgBox "The Access file doesn't exist!", vbCritical, "Invalid Access file path"
        Exit Sub
    End If
    
    'Set the name of the table you want to add the data.
    accessTable = "ProgressData"
                
    'Set the worksheet that contains the data.
    On Error Resume Next
    Set sht = ThisWorkbook.Sheets("Progress_Data")
    If Err.Number <> 0 Then
        MsgBox "The given worksheet does not exist!", vbExclamation, "Invalid Sheet Name"
        Exit Sub
    End If
    Err.Clear
        
    'Find the last row and last column in the given worksheet.
    With sht
        lastRow = .Cells(.Rows.count, "B").End(xlUp).Row
        lastColumn = .Cells(1, .Columns.count).End(xlToLeft).Column
    End With
    
    'Check if there are data in the worksheet.
    If lastRow < 2 Or lastColumn < 1 Then
        MsgBox "There are no data in the given worksheet!", vbCritical, "Empty Data"
        Exit Sub
    End If
        
    'Create the ADODB connection object.
    Set con = CreateObject("ADODB.connection")
    
    'Check if the object was created.
    If Err.Number <> 0 Then
        MsgBox "The connection was not created!", vbCritical, "Connection Error"
        Exit Sub
    End If
    Err.Clear
    
    'Open the connection.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessFile
    
    'Create the SQL statement to retrieve the table data (the entire table).
    Sql = "SELECT * FROM " & accessTable
    
        
    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.Recordset")
    
    'Check if the object was created.
    If Err.Number <> 0 Then
        Set rs = Nothing
        Set con = Nothing
        MsgBox "The recordset was not created!", vbCritical, "Recordset Error"
        Exit Sub
    End If
    Err.Clear
             
    'Set the necessary recordset properties.
    rs.CursorType = 1   'adOpenKeyset on early binding
    rs.LockType = 3     'adLockOptimistic on early binding
        
    'Open the recordset.
    rs.Open Sql, con
        
    'Updates the records from Excel to Access by looping through the rows and columns of the given worksheet.
    For i = 2 To lastRow
        rs.AddNew
        For j = 1 To lastColumn
            'This is how it will look like the first time (i = 2, j = 1):
            'rs("FirstName") = "Bob"
            rs(sht.Cells(1, j).Value) = sht.Cells(i, j).Value
        Next j
        rs.update
    Next i
        
    'Close the recordet and the connection.
    rs.Close
    con.Close
    
    'Release the objects.
    Set rs = Nothing
    Set con = Nothing
    
    'Re-enable the screen.
    Application.ScreenUpdating = True

    'Inform the user that the macro was executed successfully.
    MsgBox lastRow - 1 & " rows were successfully added into the '" & accessTable & "' table!", vbInformation, "Done"

Excel, no table simple fields