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. 

danilang

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