Modify SQL Query: Action on skipping item within Ms Access DB

[JBarbot]

Hi Guys,

Any idea, why the SQL Query is not working as expected when connected to Ms Access DB? Any help is much appreciated.

I attached a word doc to explain in details issue encountered.

Many thanks

Julian

[Vinod28]

Hi @JBarbot ,

It appears that your SQL query isn’t reflecting the updated data source, even after dynamically changing it from Comp001\Om_company.mdb to Comp200\Om_company.mdb. Here are some steps to troubleshoot the issue:

Possible Causes & Solutions:

1. Connection Not Refreshing:
   Alteryx may not be updating the connection with the new data source. Ensure the formula for the connection string is correctly applied before the Dynamic Input tool is executed. The formula should look like this: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + [DynamicPath] + "\Om_company.mdb;User ID=admin;Password=;"
   This ensures the connection string is dynamically set to the new data source.

2. Post SQL Not Running Correctly:
   The Post SQL statement might be running too late in the workflow. Make sure the connection is updated before the SQL query is executed.

   Example SQL query for the new database:

   SELECT * FROM YourTable
3. Suggested Next Steps:

   • Double-check the dynamic connection formula to ensure it’s applied properly.

   • Confirm the Input Data Source Template is updated to reference the new source.

   • Test with a static path to isolate the issue and confirm if the dynamic path is the problem.

   For further assistance, check these helpful community resources:

   https://community.alteryx.com/t5/Tool-Mastery/Tool-Mastery-Dynamic-Input/ta-p/24946 
4. https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Change-database-in-SQL-connect...