Access not properly translating TOP predicate to ODBC/Oracle SQL
I think this may be less an ODBC challenge and more an Access challenge you are facing.

The driver is more or less (with differences like when drivers like to introduce server-side cursors and break work up that way, etc) doing what the client tells it to.

Access is really well known for doing some interesting things to decent databases and somewhat well designed DBs. Can you at least put your code into Oracle in stored packages/procedures and then just call those from the forms app? Are you 100% married to Access?