[AccessD] Access 2000 - Filtering SQL Server records

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

-- 
Stuart

On 16 Sep 2009 at 10:05, jwcolby wrote:

> I have an application that goes to SQL Server for three different tables.  These tables are from a 
> third party payroll system.  The tables are about check information.  One of the fields holds a text 
> representation of the autonumber (long int) PK from one of my tables (the Claimant table).  ATM we 
> have a single view in SQL Server that pulls each and every check record.  Back in Access I then 
> convert the string to long and then filter down to just the records for the claim(s) I am interested in.
> 
> This is sloooooowwwwwww.......
> 
> In this case I am using one single view for everything I do with this check data anywhere in this or 
> any other of my applications.  Thus it returns every check (over 100K) any time I do ANYTHING.
> 
> I don't really use SQL Server very much as a BE to Access applications so I don't have experience in 
> other ways to do this.  So I need help.
> 
> I know that the first thing I could do is rewrite the view to do the data type conversion out in SQL 
> Server and hand me back records with that PKID already converted to a long integer.
> 
> Aside from that though, I really want to pass in a specific PKID and have SQL Server give me back 
> just the check records for that PKID.
> 
> Remember that this is 2K.  The data is NOT editable so I do not need, and in fact cannot even get 
> write access to the data.
> 
> So how do I get data back from SQL Server specific to a PK?  I assume that I have to go with a 
> stored procedure?
> 
> Any help (as detailed as possible) is much appreciated.
> 
> -- 
> John W. Colby
> www.ColbyConsulting.com
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com