If you want a second data source in the workbook that - ultimately - points to the same underlying data source twice, go to the Data Source tab and select Data > New Data Source and point it to the Access database again (maybe naming each connection relevant to the query function/purpose it has).
When you go to a new sheet, you should see them all and select the data source you want to use on the sheet:
If you want to create a worksheet once but point it to different connections, then you'll have to use the approach you've used where you copy the sheet into a new workbook, change the source, copy it back.
There's no way to replace data source for one single sheet. You might want to upvote this idea - https://community.tableau.com/ideas/1156
Thanks Tom for confirming for me. There should be a box to check for "data source for this sheet only" or something similar.