[AccessD] Feeding parameters to SQL Server

Eric,

Thanks for your offer of help.   Given your example:

SELECT
	field1,
	field2
FROM myAccessTable a
INNER JOIN
(
	SELECT * FROM dbo.MyFunction(param1, param2)
) b
ON a.accessField = b.functionField

What causes Param1, Param2 to be sent from Access to SQL Server?  Does the
parent query do this?  If so, what do I do in that query to tell it to
expect to be asked for Param1, Param1 and what to send in response to the
request?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Wednesday, February 21, 2007 2:24 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Feeding parameters to SQL Server

John,

Yes, it can be done with the SQL user-defined function. Let SQL server do
all the work (check for null, filter records between date parameters) before
it sends you the records across the wire. The UDF will accomplish this very
nicely for you. Let me know if you need help in moving forward with this
direction.

Eric

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Wednesday, February 21, 2007 11:11 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Feeding parameters to SQL Server

Well... I dunno.

I have a view in SQL Server returning 8 fields, 6K records (currently),
completely unfiltered, all records in the table.  I pull that linked table
into a query in order to do some preliminary filtering - check date between
a pair of dates and check number not null.

I want the view in sql server to do the "check number not null" for me, and
I know how to do that.

I want the SQL Server to take a pair of parameters - "From Date" and "To
Date" and return only those records.

If I could do this, then I could get rid of my "base query" in access which
applies these filters, reduce traffic across the wire etc.  However even if
I do this, the view is not being requested by a form in XP (for example)
where some magical thing happens in Access to pass the parameters off to SQL
Server.  The linked view will be used immediately in another query which
joins this view to other tables (on one field - BEID - in the view) in order
to build up a set of data for a report.

So the "parent" query that is using the view will have to "feed" the
parameters to the linked view.  Being a nubee to SQL Server I do not even
know if what I am attempting to do is possible.  I cannot wrap my mind
around how a query that is using a view can know that the view needs
parameters, and if it did, what to feed it.  If it is possible, I have never
done so obviously, so I don't even have the basics of what to set in Access
to do the parameter passing.

John W. Colby
Colby Consulting
www.ColbyConsulting.com