Determine if record exists when updating Access database using Excel VBA

Question

I am trying to update records, or create records if the unique ID does not exist.

The code gives me an error telling me that it would create duplicate values.

I need to include this in my code "SQL: If Exists Update Else Insert".

Sub Upload_Excel_to_Access()

Dim wbpath As String

wbpath = Application.ActiveWorkbook.Path

Dim con As Object '' ADODB.Connection
Set con = CreateObject("ADODB.Connection") '' New ADODB.Connection
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=\\at\HRS SD Performance Data\Capacity DB.accdb;"
con.Execute _
"INSERT INTO AssigenedVol_tbl " & _
"SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\luga\Desktop\Databasetest\DB Macro Test.xlsm].[rawdata$]"
con.Close
Set con = Nothing
End Sub

The table name is "AssigenedVol_tbl"

Fields are: Process_Identifier, Login, Volume, effDate, ID_Unique (This is the primary key in the database)

You should check which unique indexes use destination table, check if rows with data you are trying to insert already exist and that in the rows you are inserting there are no rows with duplicated key values. Without data we are not able to check what's wrong, the code looks fine — Sergey S.
– Sergey S., Commented May 31, 2019 at 5:51
Also, you can temporarily remove unique indexes from destination table, execute insert and then analyze where appeared duplicates in columns, where were unique indexes. — Sergey S.
– Sergey S., Commented May 31, 2019 at 5:56
I think other db such as SQLServer and MySQL can have conditional SQL for update/insert, Access doesn't really have that but it can sort of be emulated. Review last answer in stackoverflow.com/questions/6199417/upserting-in-ms-access — June7
– June7, Commented May 31, 2019 at 6:53
Why not set link to worksheet from Access and code in Access? Why is Excel involved at all? — June7
– June7, Commented May 31, 2019 at 7:53

Eric Hofer · Accepted Answer · 2021-11-19 13:44:44Z

Modify the insert statement to check for the existence of the key. Given what you explained, that would be

Sub Upload_Excel_to_Access()

  Dim wbpath As String

  wbpath = Application.ActiveWorkbook.Path

  Dim con As Object '' ADODB.Connection
  Set con = CreateObject("ADODB.Connection") '' New ADODB.Connection
  con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=\\at\HRS SD Performance Data\Capacity DB.accdb;"
  con.Execute _
    "INSERT INTO AssigenedVol_tbl " & _
    "SELECT SRC.* FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\luga\Desktop\Databasetest\DB Macro Test.xlsm].[rawdata$] " _
     & " AS SRC " _
     & "WHERE NOT EXISTS (select 1 from AssigenedVol_Tbl CHK WHERE CHK.ID_Unique = SRC.ID_Unique)"
  con.Close
  Set con = Nothing
End Sub

Note, the &'s - were done just to focus on the fact that your SRC table is being labelled, and you're checking it as CHK.

Collectives™ on Stack Overflow

Determine if record exists when updating Access database using Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related