Pulling ODBC Records From Huge Database

[richlocus]

Hello:

I have an access query that pulls records from an ODBC connected Microsoft SQL Server. The table I'm pulling contains a staggering 3 million records. The records are invoice line item histories starting in year 2003. I only need to look at the current year (2022). I put a filter in the query builder criteria for the year 2022. It seems that the query still scans through all the 3 million records (but only accepts 2022). Is there a way to stop it once it has satisfied the year requirement?

Somehow we need to cut down the time it takes to satisfy bringing in only the 2022 records from the remote server.

Is this possible going through ODBC?

Thanks,
Rich Locus

[HansV]

If you create a standard query, Access has to process all 3 million records to evaluate the criteria.
Try creating a pass-through query (see the link I posted in your previous thread). A pass-through query will be evaluated on the SQL Server, and only the results will be sent to Access on your PC.

[richlocus]

Hans:
Thanks... I will review and attempt to implement.
Rich

[richlocus]

Hans:

Thanks again for the life-saving advice regarding the pass-through query! So much faster!!! It was somewhat challenging trying to find the ODBC Connect String and the Data Source Name to set up the Pass Through parameters, and the syntax of the query needed to be modified for literal constants (i.e. street address). Also I didn't realize that in ODBC, a table name appends a dbo_ in front of every SQL Server table name. After searching, I found that I had to remove the "dbo_" in front of the query table names. All in all, it wasn't too difficult once I made it through the hurdles listed above.

Thanks again!!
Rich Locus

[HansV]

Good to hear that you were able to solve it!