Update data in an Access table from Excel with VBA

Question

I insert data into an Access table from a row in Excel using the code here (Insert Data from Excel into Access).

I have a problem when inserting a record that is already in the table because of the same primary key value.

I want to update the record or delete the record and enter a new one with the same key.

Dim Connect As ADODB.Connection
Set Connect = New ADODB.Connection

Connect.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=C:\Users\ExcelFileName.xlsm;Extended Properties=Excel 8.0;" & "Persist Security Info=False"
Connect.Execute "INSERT INTO tbl_TCM_MAA_Data IN 'C:\DataBaseFileName.accdb' SELECT * FROM [SheetName$A2:O2]"

Connect.Close
Set Connect = Nothing

First run a select query to see if the id already exists, then run either an update or an insert depending on the results — Tim Williams
I'm not sure where to add that code or even how. I'm not very familiar of using SQL in VBA. But I added the code that I have for now. — afrocode

WIL WIL · Accepted Answer · 2020-05-31T10:00:36

xlFilepath = Application.ThisWorkbook.FullName

Set Var = Sheets1.Range("A1")

Connect.Execute "Delete * FROM [TableName] WHERE [Column Name] = " & Var & ""

SQL = "INSERT INTO tbl_TCM_MAA_Data " & _

"SELECT * FROM [Excel 12.0 Macro;HDR=YES;DATABASE=" & xlFilepath & "].[SheetName$A2:O2] where [Column Name] > 1"

Connect.Execute SQL

Warning in your Datasheet Table The first 8 rows. Assuming theres a Heading (HDR=YES) the next 6 rows should contain a dummy data to define your columns equivalent to your access table column definition.

Update data in an Access table from Excel with VBA

1 Answers