Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Append Alteryx Results to Microsoft Access TAble

crouchsonia
7 - Meteor

How can I get my output from my Alteryx to append to an Microsoft Table.  I know I can do a union to bring them together, but I can't figure out how to map by fields so it appends my new data to the correct fields in my Access table and adds it as a new record.

7 REPLIES 7
steven-barsalou
8 - Asteroid

There are two ways to accomplish this that I can think of right now.

 

1 - Setup a DSN connection and connect to the database as you would any other ODBC data source.

2 - Use OleDB to connect to the Access database. This is the option that I would recommend as it doesn't require the DSN to be configured on the computer where this workflow will run.

 

To connect to an Access database using OleDB:

1 - Determine if you are using a 32 bit version of Access or a 64bit version.  In my case, I'm using a 32 bit version of Access.  

2 - In the Output Tool, use configuration drop down to select Other Databases->32-Bit Database Connections->OleDB

3 - Configure the OleDB connection.  Select the Microsoft Office 12.0 Access Database Engine OLE DB Provider, input the full path of the Access database in the "Data Source" field, enter the table name you wish to write to.

4 - Once you have configured the connection, set the Output Options to "Append Existing".

 

See the attached example workflow, database, and screenshots.

 

-Steven

 

 

 

 

crouchsonia
7 - Meteor

Thanks for your help.  I was able to use the 2nd Option.  Where do I put how the want the data to append by field.  Example, Alteryx Created_Date append to Access Date.  Do the fields need to have the exact names.  Also where do I tell it which Table to use in the database.  Should I have put that in the box that popped up and wanted an Output Table name?

steven-barsalou
8 - Asteroid

On the attached PDF from the previous post, there is an image of the Output Tool Configuration (step#4).  On line 5 of the configuration is the Append Field Map.  This is where you tell the output tool how to map the fields.  I always set the names of the fields that the exact same name as the fields in the target database.  There is an option for a Custom Field Map, but it is currently disabled in the example I've created.  I've never used that, so I can't speak to that.

 

append field map.png

 

 

The output table name should be input in the popup box that you mentioned, but you can also edit that in the connection string.  The table name is the last item in the connection string.  In the connection string of the example I created, "employee" is the table name, which can be seen at the very end of the example connection string below:

32bit:odb:Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Temp\example_database.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=__EncPwd1__;Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=__EncPwd2__;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False|||employees

 

table name.png

crouchsonia
7 - Meteor

Thanks again for your help.  The piece I was not doing properly was naming my table.  Everything is working exactly as expected.  

steven-barsalou
8 - Asteroid

Good to hear that helped solve your problem.

kmickelson
8 - Asteroid

I've been having an issue appending data to an Access Database, and tried following this guidance, but am still getting the same error (Error opening query: Microsoft Access Database Engine: No value given for one or more required parameters.)

 

I can create a table/overwrite a table just fine, but when I'm trying to append I keep getting this error. I don't know what required parameters I would be missing. Any suggestions?

Ashish_BOA
5 - Atom

Is anyone responded ? Or did you get any solution for it. Please share

Labels