I was pretty sure it would, and would be quick. On your question about needing two ODBC data sources, yes you would need one for each database that you are going to link tables from.
It seems that most developers who start with the .adp approach prefer it, although there are some limitations, such as not being able to store queries locally in the front-end. On the other hand developers who started with the .mdb approach usually prefer it, although you can run into performance problems with linked tables.
On the subject of performance with linked tables, as long as you are joining only two or three tables in a query, it will generally not be handled by Jet, but by the ODBC interface which transforms the SQL statement to what SQL Server needs to execute the query. If you get into a situation involving more tables, performance gets dicey as Jet must bring all of the data to Access and then execute the query. But with some care in design, we create complex forms and reports involving tables that contain several million rows, and get sub-second response time at the user workstation.
You can't see the view if you don't climb the mountain!