Insert or Update excel data to Access table via VBA

[sophannaly]

Hi,

I would like to insert data from excel to access table. This is the code that I use to write data from excel to access

DoCmd.TransferSpreadsheet acImport, , tableName, filePath, True, range:="Sheet1!A:" & endCol

But how to update if record already exist in access table, and if it is not exist, then insert directly?

Best regards,
Sophanna

[jimatqsi]

Sophanna,
By changing 'acImport' in that code to 'acLink' (and providing a new, temporary table name) you will add the Excel sheet to your DB, and it will look like a table. You can run a query against it just like a table. So link the spreadsheet, write an update query (or write some VB code to accomplish same) and you're done.

Jim

[sophannaly]

Hi Jim,

Thanks you so much for reply. So to do this, after importing this spreadsheet to temporary table with code DoCmd.TransferSpreadsheet, then the next line code is a query about updating data from this temp table to existing table.

Is it what solve this issue?

Best regards,
Sophanna

[NeoPa]

The steps are the right ones Sophanna. Clearly, you must do each step correctly, but if you do that then it should work.

[sophannaly]

@NeoPa, @Jim thanks you guys so much for reply. I will give a try and will come back if I have problem.

[NeoPa]

Let us know if it works too Sophanna, then I can make sure Jim's post is flagged as Best Answer.