Updating or Adding a Record in Access with Excel VBA

L

luisvv93

New Member
Joined
May 31, 2019
Messages
4
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:


Code: 
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Try this macro, which does a bulk update and insert from Excel to the Access table.

Code: 
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:
Code: 
    ExcelTable = "[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & ThisWorkbook.FullName & "].[rawdata$]"
 
Last edited:
Upvote 0
Hello John_w,

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

Code: 
'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
 
Upvote 0
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
 
Upvote 0
John_w said:
Try this macro, which does a bulk update and insert from Excel to the Access table.

Code: 
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:
Code: 
    ExcelTable = "[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & ThisWorkbook.FullName & "].[rawdata$]"
Click to expand...
my question may be lame what is the rawdata$ in the connection string to excel table
 
Upvote 0
Hey guys,
Just to confirm the functionality, the sheet in excel only has two fields (columns) ID_Unique and Volume, right?

Thanks

Cheers

T.
 
Upvote 0
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.
 
Upvote 0
antoniodelaconcha said:
Just to confirm the functionality, the sheet in excel only has two fields (columns) ID_Unique and Volume, right?
Click to expand...
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?
Click to expand...
If the named range is "DataRange":
VBA Code: 
ExcelTable = "[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & ThisWorkbook.FullName & "].[DataRange]"
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Recording Excel ranges from several tabs into Access DB
Replies
1
Views
581
alansidman
alansidman
T
  • Question Question
Error with executing SQL
Replies
9
Views
751
RoryA
RoryA
L
  • Question Question
upload acces database specific columns from excel which have same but uploaded columns
Replies
0
Views
883
lebedan
L

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top