Hallo Andrè, deine Ausführungen sind ja doch sehr langatmig.
Suche doch mal zum Thema Access in dieser Datenbank.

Meine Erfahrungen mit ODBC sind sehr gut.
Die meiste Arbeit erledigt die AS/400 und liefert tatsächlich nur die benötigten Daten.
Ob eine Abfrage performant ist, testest du am besten mittels SQL auf der AS/400, den SQL-Befehl kann man sich in Access ja ganz gut ansehen.

Vorsicht ist natürlich mit Cross-Joins, Transform und Pivot gegeben, da ggf. wirklich alle Sätze zum Client übertragen und ggf. wieder verworfen werden.
Das gleiche gilt für Verknüpfungen unterschiedlicher Datenherkunft (lokale Tabelle und AS/400-Tabelle) sowie für Abfragen auf Abfragen.

ODBC über den SQL-Server würde ich nicht empfehlen, da durch die doppelte Umsetzung Performanceverluste möglich sind.
Direkter ODBC ist erheblich schneller.
Damit nicht bei jedem User die ODBC-Verbindung manuell erstellt werden muss, kann man in einem Access-Modul auch DAO.RegisterDatabase verwenden.

Solange der Benutzer an den Abfragen nicht rumbasteln kann, können alle Abfragen auf ihre Performance vorher getestet werden.
Sollten Zugriffspfade fehlen, können diese jederzeit (ohne zu Lasten der bestehenden Anwendung) erstellt werden.