Yes, as long as your loca server is accessible from outside the network or at least from the same location the app is deployed on.
Have in mind that this functionality is not specific to Shiny or even implement by it but rather by the database engine you use.
I think you are a little confused about the role R (shiny, dplyr, etc) is going to play in your application. The database engine is independent of the application and
the access management is carried on by the database engine so, this is not related to Shiny.
You can use dplyr like syntax that gets translated to sql commands in the background by using dbplyr but it has limitations and using SQL directly would be more efficient.
Yes, as long as you implement it but there is no "one-click" solution for that.
Yes, it makes a lot of sense, I use shiny as a GUI to interact with databases very extensively but I don't think you can compare Shiny with Access they are two completely different pieces of software.