To answer your questions:

1) How exactly would I use a recordset to do things in the front end app? I couldn't come up with a scenario like that. Recordsets always interact with the back end, do they not?
3) Isn't that only included in the Enterprise version of SQL server? I will be using the Express version. What exactly does it mean, distribute my reports on the web?
1) You can create a temp dataset, alter it in a loop in memory and then send the result in one set/batch to an output device (ex. Excel) or the back-end.
3) Reporting is included in the express version, but you need to download and install it separately. Distribute on the web: once the reports are published the user can open the report on the website and then download it in the format he/she wishes (excel, pdf, xml, ...)

One remark to:
Keep them in sync with an update routine every time the database opens.
Normally you only close a SQL database for maintenance. Otherwise you keep it running, don't forget that each time you stop a SQL server (every version) you need to rebuild the statistics from scratch, making your database slower.
I hear a lot that moving a database to SQL server doesn't increase the performance, but in fact it can because you have a lot of tuning possibilities in SQL server that you haven't got in Access. In most cases it is the network traffic and slow connections that are the problem. But query performance on a SQL server can be much better than query performance in Access, even for queries with only 1000 records or less.