Access not properly translating TOP predicate to ODBC/Oracle SQL

I have a MS Access query that is based on a linked ODBC table (Oracle).

The DDL for the underlying Oracle table is:

create table road_insp
(
    insp_id integer,
    road_id integer,
    insp_date date,
    length number(10,2)
);

INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (1, 100, to_date('1/1/2017','MM/DD/YY'), 20);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (2, 101, to_date('2/1/2017','MM/DD/YY'), 40);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (3, 101, to_date('3/1/2017','MM/DD/YY'), 60);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (4, 102, to_date('4/1/2018','MM/DD/YY'), 80);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (5, 102, to_date('5/1/2018','MM/DD/YY'), 100);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (6, 102, to_date('5/1/2018','MM/DD/YY'), 120);

select * from road_insp

   INSP_ID    ROAD_ID   INSP_DAT     LENGTH
---------- ----------   -------- ----------
         1        100   17-01-01         20
         2        101   17-02-01         40
         3        101   17-03-01         60
         4        102   18-04-01         80

         5        102   18-05-01        100
         6        102   18-05-01        120

And the local MS Access Query is:

SELECT ri.*
FROM user1_road_insp AS ri
WHERE ri.insp_id = (
                    select 
                        top 1 ri2.insp_id   
                    from 
                        user1_road_insp ri2 
                    where 
                        ri2.road_id = ri.road_id 
                        and year(insp_date) between  [Enter a START year:] and [Enter a END year:]
                    order by 
                        ri2.insp_date desc, 
                        ri2.length desc,
                        ri2.insp_id
                   );

The Problem:

The performance of the query is quite poor. Unlike the table in the sample DDL, t

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?

StackExchange Database Administrators Q#189441, answer score: 2