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)

Hedgepeth

Did you really use misspelled word in table name? – Anonymous

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 – Tav

Also, you can temporarily remove unique indexes from destination table, execute insert and then analyze where appeared duplicates in columns, where were unique indexes. – Tav

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 https://mcmap.net/q/1038454/-upserting-in-microsoft-access – Anonymous

Why not set link to worksheet from Access and code in Access? Why is Excel involved at all? – Anonymous

So I have an excel that is filled by some users about 3 times per day, the user inputs data like ProcessID, Volume, Date and login, I have a vba code that exports that data when they click a button into a csv file, those files feed a Power BI dashboard however I want to migrate to access data base instead of csv files, the code above does insert the data in the access data base but it does not update the data if the primary key is already in the data base :( there is where i need help. – Hedgepeth

I tried to apply the method described in link in previous comment and it just errors when Excel is involved. It does work with tables in Access. That is why I suggested managing this from the Access side. So I think your code will have to run separate INSERT and UPDATE actions. I have yet to test an UPDATE that uses Excel as source. Why not build an Access frontend GUI for user data entry? Get rid of Excel. – Anonymous

Possible duplicate of VBA code to update / create new record from Excel to Access – Anonymous

Including a WHERE clause in INSERT action eliminates error and creates new records in table "WHERE ID_Unique NOT IN(SELECT ID_Unique FROM AssigenedVol_tbl)". Updating is more complicated. A single UPDATE action statement won't work. I gave it a try. Apparently, solution requires opening a recordset of Access table and looping records. – Anonymous

Thanks June7, I got help from this other post but I still have another issue: mrexcel.com/forum/general-excel-discussion-other-questions/… it seems that your approach it the rigth one however I am getting an error in the Insert statement... – Hedgepeth

the error is " "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicates entries and try again." Run-time error '-2147467259 (80004005)' " but I already have the statement WHERE X.ID_Unique NOT IN (SELECT ID_Unique FROM AssignedVol_tbl) so I dont get it, I already review the fields in the data base as well and they are ok the primary key is ID_Unique – Hedgepeth

Post that resolved my issue mrexcel.com/forum/general-excel-discussion-other-questions/… many thanks to all !!! – Hedgepeth

You can simulate an UPSERT in Access: see https://mcmap.net/q/1038454/-upserting-in-microsoft-access – Arsenal

I think you can also achieve this by spliting up the functionalities of your methode. Can you first check if the ID exists with a simple where statement, and then if not , proceed to the insert statement ? – Oslo

Answer 1

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.

Opia