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

An alternative is to link the Excel worksheets to Access. In this manner, the linked worksheets appear in the database as tables and can then be used as record sources for your queries. Each time you update the worksheets, the database is automatically updated because they are linked. No need for VBA or importing required. It is all in real time.