Filtered Pass Through query in MS Access

Question

I have more than 5000 records in my tblT1 I execute below queries in access 2010 with linked tables on MS Sql server database for my report.

1. Pass Through Query:

   PTQuery1 = "SELECT tblT1.* From tblT1;"

But I need to execute above Pass Through query based on criteria (tempQuotationNo).

My friend suggested me below query:

2. A Select Query based on Pass Through Query:

   Query1 = "SELECT PTQuery1.* FROM PTQuery1 WHERE PTQuery1.QuotationNo = tempQuotationNO;"

tempQuotationNO can be a variable or a field in a form.

As I know both queries load all records of tblT1 to client (second query filter result of PTQuery1 after loading). Both work fine but I need to reduce the time of loading records to my client.

Is there anybody to suggest a solution for me?

The aim is (first) to filter the records on SQL Server then load the result to FE, based on a field in a form or user defined variable to reduce the time of loading data from SQL Server to client.

Answer 1

Simply modify the PT query like this:

Dim strSQL As String
strSQL = "select * from table where QuotationNo = " & lngQuoteNum
CurrentDb.QueryDefs("MyPass").SQL = strSQL

At this point you can launch your form etc. it will use this NEW sql for the pass though. The above assumes longQuoteNum is a VBA var. If quote number is text, then you need this:

strSQL = "select * from table where QuotationNo = '" & lngQuoteNum & "'"