Append and update SQL table (is this an SSIS task or VBA) within MS Access?

[03Explorer]

I have an Access database that is being used for logic and administrative. The Data is stored in SQL Server. I am not sure if I am asking in the right place. (VBA or SSIS)

My objective is I have a (SQL) table with three fields (aID-autoID, Name-text, Active-Yn)

Business created (and will do somewhat often/annually) a new table, aside from the original (Typically built independently of original table content).

1. I need history to stay in the original table.
2. importing New [name] gets appended with new [aID] and [Active] set to Y
3. Old [name] have [Active] set to Y
4. after append, if new importing table does NOT contain an old [name] then [Active] set to N

Thinking this is to be either SSIS or VBA coding...
first question: Am I asking in the best place to make the import happen with the extra logic? (VBA) or SSIS
second question: I am looking for insight on how to make this logic happen. My mind is spinning with how to make this happen.

Thank you

 

[simian336]

I would say that if you are comfortable programming in Access, I would keep it there in Access. I would not bring in a third component unless I had too.

Simi