Like SQLSister I've also made the jump from Access, but use both of them now.
From the Access perspective (slightly OT for this forum, but relevant if you do the client side of apps).
Connect from your client app with ADO (or ADO.NET for .NET based languages) to the server as the app starts up. You can use the ADO error codes to check for invalid usernames/passwords and other connection errors.
Have one public connection object in its own module, connect at startup and then execute your data access requirements as command objects against this, picking up the results and acting on them where necessary. Be sure to close it on exiting the application though.
In recent versions of Access, forms can accept ADO recordsets as well as DAO recordsets (which is what you use with Access local tables). I've not tried this with reports in depth though, but hope it would be the same.
If you need a query as such, use a form in datasheet view, it gives you the same functionality plus the ability to handle VBA.
By using parameterised stored procedures for database access and ADO command objects, the account that is used to connect to the DB only needs execute permissions on the SP's, so you instantly remove the possibility of being able to access the data directly. Use this ability to have server side data validation in your code.
If you get involved in SQL server admin, remember that the permissions that SQL Server itself has on the server are those of the service account - this is why it can't see local mapped drives, your own email MAPI profiles etc.
If you do this properly, there will be no need for linked tables in the app. If you open linked tables from SQL Server in Access, a lot of records get locked, and this hampers performance a lot. Some queries require dragging all the data across the network.
As an idea of the performance that can be improved, by totally rewriting a linked table based reporting system using these techniques (sometimes referred to as unbound forms), I knocked the run time of some of the queries down from 3 minutes to under 3 seconds. This was for returning a recordset with 130 rows which is appalling.
Looking at SQL profiler running the old version of the system, it was sending several 10,000 row tables across the network then joining them on the client to a local table in access, no wonder.
John