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:

Process_Identifier	Login	Volume	effDate	ID_Unique
O1FA73	rodrhen	0	5/31/2019	E2-anriz-I1FA05-05/30/2019
O1FA76	jriz	0	5/31/2019	cmposc-I1FA05-05/30/2019
I1FA05	jriz	10	5/31/2019	cmposc-O1FA73-05/30/2019
O1FA73	cmposc	10	5/31/2019	jriz-I1FA05-05/30/2019
I1FA05	cmposc	0	5/31/2019	jriz-O1FA76-05/30/2019
I1FA05	anriz	20	5/31/2019	rodrhen-O1FA73-05/30/2019
I1FA05	luiveg	90	5/31/2019	luiveg-I1FA05-05/30/2019

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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