Delete selected rows from MS Access table

[John_Abraham]

Hello .. I am processing a spreadsheet with records for a given month, which needs to be updated into two Microsoft Access tables. One table contains the data only for the current processing month, so I do a Delete and Append, and it works fine. The main table contains all historical records. I want to delete any records for the current processing month, if they exist. I can delete all records from the main table, where the yyyymm matches the records in the 1st table. So I have incorporated the below code.

The Block Until done sequence is

1. Delete and Append into the current month's table, works fine
2. Delete records for the current processing month from the main table if exists (this is needed for a potential re-run) - issue in deleting records
3. Append records into the main table, works fine

Questions

1. First of all .. is the above a decent approach or are there better / more elegant ways of doing this ?
2. I created a query in Access to delete records from the main table, joining to the 1st table. This query works fine in Access, but how do I code this in Alteryx ? I don't think I can use output data tool. When I use the first two output options, I get the error - Microsoft Access Database Engine: An action query cannot be used as a row source. The last two options are changing my query into a table, and not deleting the rows.  

Thanks for your help. 

[hroderick]

could you pull the whole table into Alteryx, remove the rows, then reload the table?

[John_Abraham]

Thanks @hroderick. Since these are not very large tables, it is definitely a possibility to pull in the whole table into Alteryx and process. This approach could be used for comparatively smaller tables only. There should be a better way to do this, especially when we need to deal with only may be 5% of the whole table.

[danilang · Accepted solution]

Hi @John_Abraham 

In a standard active database, SQL Server, Oracle, you'd use the Post SQL functions in the output tool.  Send in a row of dummy data that includes the key that you'll use to in the delete statement.  Put your delete statement in the Post SQL.  If your delete statement is something static like 'Delete From Table1 where Datefield >= DATEADD(month, DATEDIFF(month, 0, GetDate()), 0)'  i.e. delete all records for the current month, you can include it directly in the Output Data.  If the delete statement is dynamic, you'll need to put the output tool in a batch macro, pass the delete statement in as the control parameter and use an action tool to update the Post SQL field as in this post

The last time I used Microsoft Access, though, it wasn't isn't an active database.  It doesn't have a service always running that executes SQL statements as they come in.   It is possible that the Jet engine will execute the delete on your behalf but you'll have to experiment.

Dan 

[John_Abraham]

Thanks Dan. I read within this community comments that the pre-sql steps happen at the beginning of a workflow. If that is true, shouldn't the control parameter needs to be built earlier as an input file or so, before starting the workflow ?

[danilang]

Hi @John_Abraham 

The Pre-SQL statements definitely run when the workflow is opened, but I don't think the Post SQL statements do.  This is something that you could research.  

Dan

[JagdeeshN]

Hi @John_Abraham ,

Please refer to the below discussion for more information on the pre-sql and post-sql queries in alteryx.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Pre-Post-SQL-Statements/ta-...

Best,

Jagdeesh