community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Meteor

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

AccessTable.png

 

OutputData.png

Fireball

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.

@Grahambo

 

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

Meteor

@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, :

 

Key.png

 

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

Meteor

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

@Grahambo

 

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 

Tab Design.PNG

below is the typical workflow i used for loading New (Insert) updating changed (exsiting)

Access WF.PNG

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
Labels