It's funny after taking the time to post in detail what I tried already I found a solution. I found out that the connection is lost only if opening up the table to view it's contents. Making the linked tables is fine. So I searched specifically for Access linked tables and the error message and found pretty much a direct hit here. It turns out I only had to click on the Last Record arrow and the connection does not close. Apparently it's a known issue that was not fixed as of yet; I'm using Access 2013.

Also, in thinking about this there isn't much use to view an entire table's contents for the most part. It's just too much information. However, if the user needs to do it the workaround it's easy.