I feel your pain; I did a similar project 9 years ago where I moved an Access 97 database to a SQL 2005 back end, maintaining the Access front end. I used parameterized stored procedures and VBA modules that grabbed the form's selections and passed them through to SQL in a dynamic SQL pass-through. Possibly not the most elegant or desirable solution, but it passed muster with our strict DBAs and the users did not have any problems with it at all.

I wish I could remember more of the details so I could help, but it has been a really long time. Years ago, there was a book about marrying SQL and Access database programming but it was written for the 2000 versions and I don't know if it was ever updated. It was a fantastic resource for me but perhaps a bit out of date for your needs. Some of those "Professional Access Programming" titles will certainly have sections devoted to using SQL Server as your back end. That SQL/Access programming book and O'Reilly's VBA In A Nutshell were lifesavers. So was Google! 😀

Anyway, I just wanted to say good luck; it may seem intimidating now but you will get your arms around it pretty quickly, especially with the knowledge base here. 🙂 Good luck!