Funny you should ask this because the newest version of Access is running SQL Server. The Jet engine is gone. It's just a really fancy GUI on top of SQL Server Express way deep under the covers.

As to the utility of it, it depends on how you program it. Yes, it works fine with sQL Server as a back-end, but, by default, no other settings modified, it takes locks on tables that make multi-user access difficult. You can just use it as a programming/reporting front end, running everything through stored procedures and, from the SQL Server side, it works as well as any other application. But, this means learning quite a bit of Access Basic (or whatever they call it these days).

So, the simplest, easiest way to use it is not the best for SQL Server. The more complex approach works great with SQL Server.