I've done this with a few applications and here are my observations/suggestions:
SQL back end works well with Access 97/2000 as front end. I have one app with the SQL DB with over 4 gig of data, some tables with over 2 millions rows of data (in each table).
I've now discovered that there is a limitation with fields that were text/memo fields in Access. The max amount of data must be limited to 254 characters (not 255...Access will choke when trying to "translate" 255 characters into a 255 character text field definition. However, the field should be nvarchar(255) in SQL, but in the Access application you need to include code to limit the actual data to 254 characters (don't ask why!). Memo fields just plain don't work. The data will be imported into SQL fine, but there is not field type of "memo" in SQL and the field type used by SQL is not recognized or handled by Access.
I looked into using Access Project (2000 and up), but there are some serious problems. Although you can define/create views and stored procedures directly with Access 2000 project, when you go to Access 2002 (and above) project you will NOT be able to create them. So if you have any part of your application that creates queries, etc., they will no longer be usable in Access Project. This eliminates a query builder I designed in Access that has proven to be extremely useful and user-friendly. (It creates pass through queries that end up being executed by SQL server, so there is efficiency because they are not executed by the Access jet engine.)
One thing that is very handy with the Access 97/2000 front end is that you can include tables that are Access tables within the front end application. The only tables to include should be ones that are used for control of the application (and data is not entered or edited by users). Since Access project does not have any "local" (i.e., stored with the application front end) tables, you lose this capability unless you put the tables in SQL server.
Except for the limitation in the size of the text field and the inability to use memo fields, it works extremely well. And no more shutting down your application in order to compact the back end (data) database! But don't forget to occasionally copy the "original" front end over the one on your server, since Access does have the annoying habit of getting slightly larger every time someone opens it.