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.)
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:\Users\JGJ\Desktop\Sanering.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.
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: