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.