Thanks for the responses.

After further development, we changed the set up in Access to just have a linked table to SQL Server that has all the data updated. I still call the DTSX (not DTS) that was created with SSIS from within Access VBA code to start the update on the server side.