Output Data Tool: MS Access - Primary Key Required for Update Option

[ShaneGraham]

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)

ODBC Version 03.51

[AndrewW]

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]

@ShaneGraham

not sure why its not working

can you provide the sample workflow and data 

or i would suggest to use LEFT JOIN technique for finding New rows then Append them into your Access file

i will upload this method in few Mins

Thanks

saravanan

[ShaneGraham]

@AndrewW, a primary key does not need to be a single field to be a valid primary key in either Access or other RDBMS's such as Oracle and SQL Server, :

When you do use more than one field in a primary key, then it is called a composite primary key, as discussed in this article:

http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

[ShaneGraham]

@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.

[s_pichaipillai · Accepted solution]

@ShaneGraham

Here is another Approach i can suggest

1. Use Left Join Output which outputs new rows and Insert
2. Add A staging table in your MS Access database and Load the JOIN outputs ( matching rows)
3. 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)

UPDATE Sample
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
WHERE
		Sample.EName <> stg_Sample.EName