Updating or Adding a Record in Access with Excel VBA

[luisvv93]

Hello All,

I'm currently using a macro that pushes data from a sheet in Excel to the appropriate column in an Access table (referred to throughout as 'AssignedVol_tbl') at the click of a button. However, the code I'm using will currently only add a new record to the database whereas I want it to be able to update a record where the data in certain fields (the primary keys of the table) already exist. Here is the code I am using:

Sub Upload_Excel_to_Access()

Dim con As Object  '' ADODB.Connection
Set con = CreateObject("ADODB.Connection")  '' New ADODB.Connection
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=\\an\HRS SD Performance Data\Capacity DB.accdb;"

On Error Resume Next
con.Execute _
        "INSERT INTO AssigenedVol_tbl " & _
        "SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\lueg\Desktop\DB Macro Test.xlsm].[rawdata$] ", dbFailOnError
        
If Err.Number <> 0 Then

con.Execute _
            "UPDATE AssigenedVol_tbl " & _
            "SET Volume = Volume " & _
            "FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\lueg\Desktop\DB Macro Test.xlsm].[rawdata$]" & _
            "WHERE ID_Unique = ID_Unique " & _
            "FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\lueg\Desktop\DB Macro Test.xlsm].[rawdata$]"
            
End If
               
con.Close
Set con = Nothing
End Sub

So let's say that ID_Unique is the primary key in Table. We already have records in the table (Process_Identifier, Login, Volume, effDate, ID_Unique ), we want to update Volume based on ID_Unique if it already exists, if the ID_Unique does not exists we want to add Process_Identifier, Login, Volume, effDate, ID_Unique data from excel to the data base.

Example of excel data:

[TABLE="width: 856"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Process_Identifier
[/TD]
[TD]Login[/TD]
[TD]Volume[/TD]
[TD]effDate[/TD]
[TD]ID_Unique[/TD]
[/TR]
[TR]
[TD]O1FA73
[/TD]
[TD]rodrhen
[/TD]
[TD]0[/TD]
[TD]5/31/2019[/TD]
[TD]E2-anriz-I1FA05-05/30/2019[/TD]
[/TR]
[TR]
[TD]O1FA76
[/TD]
[TD]jriz[/TD]
[TD]0[/TD]
[TD]5/31/2019[/TD]
[TD]cmposc-I1FA05-05/30/2019[/TD]
[/TR]
[TR]
[TD]I1FA05[/TD]
[TD]jriz[/TD]
[TD]10
[/TD]
[TD]5/31/2019
[/TD]
[TD]cmposc-O1FA73-05/30/2019[/TD]
[/TR]
[TR]
[TD]O1FA73[/TD]
[TD]cmposc[/TD]
[TD]10[/TD]
[TD]5/31/2019[/TD]
[TD]jriz-I1FA05-05/30/2019[/TD]
[/TR]
[TR]
[TD]I1FA05[/TD]
[TD]cmposc[/TD]
[TD]0[/TD]
[TD]5/31/2019[/TD]
[TD]jriz-O1FA76-05/30/2019[/TD]
[/TR]
[TR]
[TD]I1FA05[/TD]
[TD]anriz[/TD]
[TD]20[/TD]
[TD]5/31/2019[/TD]
[TD]rodrhen-O1FA73-05/30/2019
[/TD]
[/TR]
[TR]
[TD]I1FA05[/TD]
[TD]luiveg[/TD]
[TD]90[/TD]
[TD]5/31/2019[/TD]
[TD]luiveg-I1FA05-05/30/2019
[/TD]
[/TR]
</tbody>[/TABLE]


The issue is that I am very new to VBA/macros/coding in general and have no idea how to execute this, so I am looking for any help, assitance, or pointer fingers in the right direction that someone could provide.

Thanks in advance for any and all insight!

 

[txchem]

I know this is an old post but I tried to use the code but I get an error on the .Execute States " Run-time Error '-2147217900 )80040e14)': Syntax error in Update Statement" When I hit debug it highlights the .Execute Statement