[AccessD] Access 2000 - Filtering SQL Server records

This got me what I needed.  I created a pass through query, then built a function to modify that 
pass through query on-the-fly to pull records for a specific claimant which is what I needed in this 
one specific subform.  The resulting recordset (20 odd records) returns in about a second or two vs 
about 20-30 seconds (60,000 records) the old way.

Plus now I have done it so I can do it again as necessary.

Thanks to Stuart and Martin for their suggestions.

John W. Colby
www.ColbyConsulting.com


Stuart McLachlan wrote:
> You need to use a "pass-through query"  
> 
> To create a *simple* pass-through query:
> 
> Start a new query in Design mode.  Don't select any table.
> 
> With the Design Grid open, Select "Query-SQL Specific-PassThrough" from the main menu.
> The SQL window will open.  Enter the appropriate query, using Transact SQL, not Access 
> SQL,   
> 
> Select Cast(chequenum as int) from tblCheques where PKID = 1
> 
> Click on View-Properties on the main menu. Enter the ODBC connection string (or click on 
> the little selector beside it and select an ODBC data source.
> 
> Now save the query as "qryGetCheckNumber"
> 
> So far so good.  
> 
> The problem comes when you want to use a different PKID,   Pass-through queries can't 
> take parameters :-(.
> 
> To pass a parameter, you need to modify the querydef so you
> need to do something like this before opening the query:
> 
> Public Sub SetChequePKID(PKID As Long)
>    Dim strSQL As String
>    Dim qdf as DAO.QueryDef
>    Dim rs as DAO.Recordset
>    strSQL = "Select Cast(chequenum as int) from tblCheques where PKID" & PKID
>    Set qdf = CurrentDb.QueryDefs("qryGetCheckNumber")
>    qdf.SQL = strSQL
>    qdf.Close
> End Sub
>