This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm trying to use the "Update; Insert if New" option against an ODBC connected Microsoft Access database table. I've made sure to create a primary key for the table, however the Output Data tool reports this error: "Primary Key required for Update option". Below are screen shots of the Access table design and the Output Data input. Has anyone seen this before and worked around it?
Thanks for any assistance.
Alteryx version 10.1 (x64)
Microsoft Access 2016 (.mdb file originally created in Access 2003)
I haven't used Access for a long time but based on the screen shots you don't have a primary key in the table, you have a set of composite keys. Try putting in a genuine Primary Key, such as an Autonumber field and see if that works. Also remove the key symbols from those 'non-PK' fields in case Alteryx gets confused leaving just one key field in the table.
@s_pichaipillai, I need to update existing records too, which is why I didn't just append new rows. None of the update options work, all failing with the same error. I'll see if I can work up some sample data that is safe to share.
Use Left Join Output which outputs new rows and Insert
Add A staging table in your MS Access database and Load the JOIN outputs ( matching rows)
Use POST SQL to compare and update the changed rows with the target
so below is the table design image Sample is your Target schema with PKeys . Stg_Sample is your staging Schema
below is the typical workflow i used for loading New (Insert) updating changed (exsiting)
below SQL used in POST SQL . here i am not updating all existing rows, but only changed rows .(where clause)
INNER JOIN stg_Sample ON (Sample.PerfYear = stg_Sample.PerfYear) AND (Sample.FCCty = stg_Sample.FCCty) AND (Sample.FCIBO = stg_Sample.FCIBO)
SET Sample.EName = stg_Sample.EName
Sample.EName <> stg_Sample.EName