# Updating or Adding  a Record in Access with Excel VBA



## luisvv93 (May 31, 2019)

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
LoginVolumeeffDateID_UniqueO1FA73
rodrhen
05/31/2019E2-anriz-I1FA05-05/30/2019O1FA76
jriz05/31/2019cmposc-I1FA05-05/30/2019I1FA05jriz10
5/31/2019
cmposc-O1FA73-05/30/2019O1FA73cmposc105/31/2019jriz-I1FA05-05/30/2019I1FA05cmposc05/31/2019jriz-O1FA76-05/30/2019I1FA05anriz205/31/2019rodrhen-O1FA73-05/30/2019
I1FA05luiveg905/31/2019luiveg-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!


----------



## John_w (Jun 4, 2019)

Try this macro, which does a bulk update and insert from Excel to the Access table.


```
Public Sub Update_and_Insert_Access_Table()
    
    Dim dbConnection As Object  'ADODB.Connection
    Dim dbCommand As Object     'ADODB.Command
    Dim ExcelTable As String
    Dim SQL As String
    
    ExcelTable = "[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\lueg\Desktop\DB Macro Test.xlsm].[rawdata$]"
    
    Set dbConnection = CreateObject("ADODB.Connection") 'New ADODB.Connection
    dbConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=\\an\HRS SD Performance Data\Capacity DB.accdb;"
    
    Set dbCommand = CreateObject("ADODB.Command") 'New ADODB.Command
    
    With dbCommand
        .ActiveConnection = dbConnection
                    
        'Update Volume field in existing records
            
        SQL = "UPDATE AssignedVol_tbl A" & _
              " INNER JOIN " & ExcelTable & " X" & _
              " ON A.ID_Unique = X.ID_Unique" & _
              " SET A.Volume = X.Volume"
        .CommandText = SQL
        .Execute

        'Insert new records

        SQL = "INSERT INTO AssignedVol_tbl" & _
              " SELECT * FROM " & ExcelTable & " X" & _
              " WHERE X.ID_Unique NOT IN (SELECT ID_Unique FROM AssignedVol_tbl)"
        .CommandText = SQL
        .Execute
    End With
    
    dbConnection.Close
    Set dbCommand = Nothing
    Set dbConnection = Nothing
    
End Sub
```
If you happen to change the file name and location of your macro workbook you could replace the ExcelTable line with this line so that it still works, regardless of its file name and location:

```
ExcelTable = "[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & ThisWorkbook.FullName & "].[rawdata$]"
```


----------



## luisvv93 (Jun 5, 2019)

Hello John_w,

It looks awesome however I am getting other error in the following statement:


```
'Insert new records

        SQL = "INSERT INTO AssignedVol_tbl" & _
              " SELECT * FROM " & ExcelTable & " X" & _
              " WHERE X.ID_Unique NOT IN (SELECT ID_Unique FROM AssignedVol_tbl)"
        .CommandText = SQL
        .Execute
```

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)'

Just for you to know the "ID_Unique" can not be duplicate. I saw that your statement only insert where ID_Unique not in the access database however it does not work  

any ideas?

thanks in advance


----------



## luisvv93 (Jun 5, 2019)

I just fixed, it was at my end however now it runs with no errors but it is not updating nor inserting data  It must be a labels issue. 

I ll keep you in the loop,

Thanks


----------



## luisvv93 (Jun 5, 2019)

After modifying some variable names the code is working awesome!!!

Thank you very much!!!


----------



## ravaz (Mar 19, 2021)

John_w said:


> Try this macro, which does a bulk update and insert from Excel to the Access table.
> 
> 
> ```
> ...


my question may be lame what is the rawdata$ in the connection string to excel table


----------



## John_w (Mar 20, 2021)

ravaz said:


> what is the rawdata$ in the connection string to excel table


The Excel sheet containing the data is named "rawdata".


----------



## antoniodelaconcha (May 29, 2021)

Hey guys,
Just to confirm the functionality, the sheet in excel only has two fields (columns) _ID_Unique_ and _Volume_, right?

Thanks

Cheers

T.


----------



## antoniodelaconcha (May 30, 2021)

Hi All,
Have another question, specifically for John_w:

How about if instead of an excel sheet as a range, I have a named range instead? See, right now I would have to copy my named range to a temporal sheet to execute the macro properly, it can be done programmatically not a big deal, but it would be cleaner if done directly with the code you kindly provided.

Cheers and thanks a lot for your help.

T.


----------



## John_w (May 30, 2021)

antoniodelaconcha said:


> Just to confirm the functionality, the sheet in excel only has two fields (columns) _ID_Unique_ and _Volume_, right?


No, 5 columns - look at the OP.


antoniodelaconcha said:


> How about if instead of an excel sheet as a range, I have a named range instead?


If the named range is "DataRange":

```
ExcelTable = "[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & ThisWorkbook.FullName & "].[DataRange]"
```


----------



## luisvv93 (May 31, 2019)

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
LoginVolumeeffDateID_UniqueO1FA73
rodrhen
05/31/2019E2-anriz-I1FA05-05/30/2019O1FA76
jriz05/31/2019cmposc-I1FA05-05/30/2019I1FA05jriz10
5/31/2019
cmposc-O1FA73-05/30/2019O1FA73cmposc105/31/2019jriz-I1FA05-05/30/2019I1FA05cmposc05/31/2019jriz-O1FA76-05/30/2019I1FA05anriz205/31/2019rodrhen-O1FA73-05/30/2019
I1FA05luiveg905/31/2019luiveg-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 (Jun 16, 2022)

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


----------

