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!

 

[John_w]

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]

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]

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]

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

Thank you very much!!!

 

[ravaz]

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$]"

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

 

[John_w]

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

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

 

[antoniodelaconcha]

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]

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]

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.

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]"