Error Importing multiple Microsoft Access tables using the dynamic import tool

[jlett]

Hello - I have been struggling trying to create a workflow that can import multiple tables from an access database.  I got it to work fine with excel files (same data i loaded into the access database) but I need to source access to solve data integrity issues.  If I have the action set to "change entire file path" error is that i need to choose a table.  If the action is set to "change file/table name" the error is that it can't find the database.  Any help would be appreciated.  

I'm running 2018.3 on desktop.  I attached the workflow and data samples below.

Thanks,

Jim

[NicoleJohnson · Accepted solution]

Hello!

Couple suggestions here:

1. Make sure that you have the necessary Access driver - see article here for instructions on where to download the driver. This will allow Alteryx to connect to Access databases (which I'm guessing is the problem). 

2. If you go the "change file/table name" route, you should just be able to provide a list of tables, connect to the actual Access database as your initial input template configuration in your Dynamic Input tool (i.e. don't connect to Excel first if you're planning on connecting to Access for your dynamic input data), and then select the field containing the list of table names as your field to "change file/table name"... If you go the "change entire file path" route, you'll need to create a list of the full file path for each table name, which would include the file location as well as the table name, separated by ||| (three pipe characters). Example: "C:\Desktop\Folder Location\Access Database Name.accdb|||Table 1", "C:\Desktop\Folder Location\Access Database Name.accdb|||Table 2", etc.

If you validate that you have the Access driver and then follow the naming conventions for feeding your data into the Dynamic Input tool, I think this will solve your input issue! Please let us know if you run into additional issues, however... 

Cheers!

NJ

[jlett]

Thank you Nicole!!!  I was able to get your first solution to work by creating an additional table within the access database that held the names of the other tables that I was trying to import (drivers were not an issue I'm on office 365).

Is there a way to just bring in all the tables in an access database without having to know their names in advance?  For instance, if you had a 1000 different tables named something different every time you ran this process...this solution we be more difficult to implement.

Thank you so much for your insight!

Jim Lett

[NicoleJohnson]

Looks like you might be able to run a query against your Access DB to get a list of the names - check out this article? 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Microsoft-Access-List-Tables/m-p/62898

If that works, you would just use a regular Input tool to initially grab the names of all tables, and then create your list of table names that would then feed into your Dynamic Input tool... 

NJ

[alex]

How would you handle it when you want each of the access tables to have its own output in a different branch of the workflow?  5 tables - all with different types of data and columns.  I can get it to pass the table name thru the dynamic input so I can filter downstream but would like to figure out how to get different sets of data without reading in each table thru its own input tool if at all possible.  Any ideas?