Excel VBA to update table in Access 2013

Hi Sumif, here's some basic code to get you started as far as opening a database connection, and deleting records from the table: (requires you to add a reference to Microsoft ActiveX Data Objects Library (I use 6.1 generally))

Its just a quick sample code, and I didn't test but use code like this quite often so it ought to work.

Sub sumif_access_update()

Dim dbs As ADODB.Connection
Dim rset1 As ADODB.Recordset
Dim sql As String
Dim i As Long
Dim ws As Worksheet


Set dbs = New ADODB.Connection

'open connection to the database:
dbs.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=X:\mydatabase.accdb"

sql = "Delete * from [myTABLE]" 'just setting the string to the sql command that will delete all records from the table
dbs.Execute sql 'executes the sql statement
sql = "SELECT * frm [myTABLE]" 'recycling the sql variable to now be used to open a recordset
Set rset1 = New ADODB.Recordset
'open recordset to do the updating
rset1.Open dbs, sql, adOpenStatic, adLockOptimistic
Set ws = Worksheets("Sheet1")
'loop through data rows
For i = 2 To 50 'just random numbers, representing the first and last row of data
    With rset1
        .AddNew 'inserts new record
        .Fields(0) = ws.Range("A" & i).Value 'instead of ".fields(0)" you could also use "![FieldName]"
        .Fields(1) = ws.Range("B" & i).Value
        'etc for all fiels in db
        .Update 'updates new record
    End With
Next i
rset1.Close
Set rset1 = Nothing
dbs.Close
Set dbs = Nothing

End Sub