Overwrite access database from excel table using VBA

Question

I've searched this website for my question, but unable to locate solution. I'm a beginner for excel VBA, Need some help help in updating old data in access database with new data from excel spreadsheet using excel-VBA.

From internet i've created the following steps.

Step1 : Uploading initial data (I've got a working VBA macro for this step):

Upload excel table:

Step 2 : Downloading data from access to excel (I've working VBA macro for this step)

Download from access:

Step 3 : Now, I want to overwrite entire access table with revised data from excel table. I'm unable to locate macro for this step.

Overwrite from excel to access:

Please help me with a VBA program or any website link for this step.

I'm unable to attach the excel sheet here.

You should clear the table before uploading the new data – Harassed Dad Jun 28 '18 at 14:37 — Harassed Dad, Jun 28 '18 at 14:37

score 0 · Answer 1 · answered Jul 05 '18 at 09:20

Finally found macro from other source, sharing the code for any other beginner

Sub Modify()

Dim dbs As ADODB.Connection
Dim rset1 As ADODB.Recordset
Dim sql As String
Dim ws As Worksheet
Dim lr As Long
Dim FName As String
Dim i As Long, j As Integer

FName = "C:\Users\temp\Database - Copy.accdb" 'change as needed
Set dbs = New ADODB.Connection
 dbs.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
 "Data Source=" & FName
Set rset1 = New ADODB.Recordset: rset1.CursorLocation = adUseClient

Set ws = Worksheets("Import") 'update sheet name as needed.
lr = ws.Range("B" & Rows.Count).End(xlUp).Row
For i = 10 To lr
    rset1.Open "SELECT * FROM [DB] WHERE [ID] = " & ws.Range("B" & i).Value, dbs, , adLockOptimistic
    With rset1
        For j = 1 To rset1.Fields.Count - 1
            .Fields(j).Value = ws.Range("B" & i).Offset(0, j).Value
        Next j
        .Update
        .Close
    End With
Next i

Set rset1 = Nothing
dbs.Close: Set dbs = Nothing

End Sub

Overwrite access database from excel table using VBA

1 Answers1