I've done a couple of projects where we upgraded Access to link to SQL server.  I would not do it again.

With .Net click once deployment I would just code the front end in your favorite (C#, VB, whatever) and query SQL server directly.  I don't use DSN's any more either.  We have a parameterized connection class that works like a charm.

Reporting?  Crystal is built into .Net these days.  There are some good thrid party tools as well.  My clients love that they can preview a report and then exort to PDF.  They then include that in an e-mail.  We have one app that automatically prepares PDF reports for client customers and e-mails to the customer and account manager at the same time.  The fact that I can, and do, write services in VB.Net makes this a snap.

They fact that I can kiss Repare/Compact on a weekly basis goodbye is enoug for me not to look back.