Access and SQL Server differ in a fundamental way. In Access all processing of the data is done by the client(s), while in SQL Server all processing (before delivering the query result) is done by the central server process.
That has several performance implications. In Access the client must transfer over the network all the data that is needed to produce the query result. Also, you don't have exclusive access to the data file on the network drive which limits the caching options on the client (it cannot easily know if the data has been modified in the meantime).
SQL Server takes exclusive access to it's (local or semi-local) datafiles and thus can cache both reads and (partially) writes, it can optimize it's file access by removing as much random access as possible and it needs only to transfer the query result over the network to the client, which is often much smaller than the data needed to produce it, especially when using aggregates like SUM
or COUNT
.
On the other hand, while SQL Server can and does optimize effectively, it has do all the heavy lifting of multiple user access within the physical constraints of the server processor(s) and memory, while Access 'only' needs to synchronize the physical data access and offloads all calculation work onto the client machine.
So, as others wrote here, if you will see performance gains or not depends on the OS and network protocol to access the Acess-DB-File(s), the stats of the SQL Server you would put in place, the number and activity of the users and the nature of the queries they perform. Maybe you have enough information to make an educated guess based on this information.