ms access 2010 - Import two columns from Excel into existing Access2010 table gives error using transferspreadsheet

Question

I have an excel spreadsheet, with 12 columns, where I only need to import data based on the last two (The rest are needed to fill out the last column.)

Excel table

Acces table

As shown on the picture I have a table in Access called "ProjektDelledning" where the two columns DelledningID and SaneringsmetKode are already present. If a DelledningID is already present it needs to update the SaneringsmetKode, if it isn't present in the table, it needs to add the value. So Delledning 258 should have a SaneringsmetKode = 1 in Acces after import and so forth.

So far I've tried using this:

Public Function Import2Columns()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "ProjektDelledning", "C:UsersJGJDesktopSanering.xlsx", True, "Concatenate!L:M" 
End Function

I get an error saying that Access was unable to append all the data to the table. The Contents of field in 0 reocrd(s) were deleted, and 0 record(s) were lost due to key violations...

It is worth noting that in the Access table, both ProjektID and DelledningID is a key.

Any help as to how I can get a correct import using transferspreadsheet or a different method works. I would like to make the import work using a Macro.

Answer 1

A method could be to import to a temp table (or just link the range).

Then, using this, run a combined update/append query.

This old tip from Smart Access is one of my favourites:

Update and Append Records with One Query

By Alan Biggs

Did you know that you can use an update query in Access to both update and add records at the same time? This is useful if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblNew into tblOld.

Follow these steps:

1. Create an update query and add the two tables. Join the two tables by dragging the key field of tblNew onto the matching field of tblOld.

2. Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from tblOld.

3. Select all the fields from tblOld and drag them onto the QBE grid.

4. For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld.

5. Select Query Properties from the View menu and change Unique Records to False. (This switches off the DISTINCTROW option in the SQL view. If you leave this on you'll get only one blank record in your results, but you want one blank record for each new record to be added to tblOld.)

6. Run the query and you'll see the changes to tblNew are now in tblOld.

   This will only add records to tblOld that have been added to tblNew. Records in tblOld that aren't present in tblNew will still remain in tblOld.