Access, SQL Server - Stored Procedures / DTS
I am not sure this is the correct forum to talk about Access / SQL Server interoperability issues. If not please point me to the correct forum.
I have an MS Access front-end (not web based) and a linked SQL-Server back-end. At the recommendation of another programmer, we created an ODBC connection so that I can treat the backend tables as linked Access tables.
My process requires that I follow the following process:
1) Delete all records from a temporary table in the backend.
2) Import a CSV (comma separated values) file into the temp table.
3) Open the table for the user to examine the records.
4) Allow the user to delete (update) records of their choosing
First Issue: I learned how to build a DTS package to do steps 1 & 2. However, I don't know how to call the DTS package from the Access front-end.
Second Issue: after the DTS package does steps 1 & 2, the linked table doesn't update to show the new set of records.
Third Issue: for reasons I don't understand, this linked temp table is not updateable ... when I open it, I can't modify, delete, or add any records.
I'm open to suggestions ... I'm not sure if this is the most efficient way to accomplish the sequence of steps I have outlined, but it appears I am missing something.
Can anyone illuminate me on how to do this OR how to do this more efficiently?
Thanks ahead of time!:)