I'm not sure there is a question in your post, but it seems that some clarification is needed. First, the major strength of Access is the ability to bind the data source to forms and reports, eliminating the need to write VBA to populate controls. However you cannot bind a pass-through query to a form or report, so that limits it's appeal. Second, the use of ODBC linked tables isn't a concern unless you are creating queries that join the linked tables. We routinely link to multi-million row tables and get sub-second response time in populating sub-forms that display a few records from the table. Finally, it should be noted that ADP (Access Data Project) support has been removed from Access 2013, although the original post in this thread did not use ADP.
Finally, since this is a 5-year old thread, it might be more useful to create a new thread on how to best deal with the fact that Access 2013 no longer supports the ADP approach, as I suspect quite a few readers would find that more interesting. My personal view is that ADP developments always took far more development time than the linked table MDB approach, and didn't give significantly better performance.
You can't see the view if you don't climb the mountain!