In the first instance Access is using a "Pass Through" query, so the query is actually passed to SQL, SQL executes it and returns the results, which Access then passes on to the APP.
In a replicated database if you SELECT something that comes from the database you are connected to. If you UPDATE something that that change is passed to all the other replicated servers, and each one updates its local table. (This usually uses a "Two Phase Commit" - the first phase is "Everybody ready?" and the second phase "Now do it" - if one of the servers disconnects between the two phases the transaction is still guaranteed to proceed - I have never understood how that is possible and it seems to me to be a Dark Art!!)
There are numerous variations-on-theme, so what I have described is very "broad brush" and it doesn't have to happen that way. For example if you are working on a Laptop, which has a local "replicated" copy of the database, you can do a quote for a client whilst you are in their office, with NO connection to your central database, and then when you get back to the office your Laptop Database will replicate to the Master Database. In this instance the replication has to allow for Collisions (someone else already created that record ...) or the product you quoted for no longer being in the Product table on the master database!!