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

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

Common Use Cases

Accepted answers

s_pichaipillai

@ShaneGraham

Here is another Approach i can suggest

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

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

MS Access.yxmd

All comments

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703