Pass through query from MS Access

I would like to call this stored procedure, but I am unable to pass
parameters to the @Start and @End.

Is thier a way to pass parameters to a pass through query from MS
Access?

SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersAct ionID, 2)
AS [Signed PP]
FROM dbo.tblPersActionLog INNER JOIN
dbo.tblPersActionHistory ON
dbo.tblPersActionLog.PersActionID =
dbo.tblPersActionHistory.PersActionID
WHERE (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND
(dbo.tblPersActionLog.Rejected = 0) AND
(dbo.tblPersActionLog.IsPayAction = 0) AND
(dbo.tblPersActionHistory.ActionTypeID = 5) AND
(dbo.fn_IsParACorrection(dbo.tblPersActionHistory.PersAction ID) = 0)
AND

([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersActio nID) = 1)
AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND
(dbo.tblPersActionHistory.ItemDTG <= @EndDate)
GROUP BY
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersAct ionID, 2)

Re: Pass through query from MS Access

On Wed, 2 Jan 2008 05:06:50 -0800 (PST), gumby
wrote:

You have to replace the parameters before submitting the query.
sql = "select ..."
sql = replace(sql, @StartDate, "'1/1/2008'")
(note the single-quotes around the date)
sql = replace(sql, @EndDate, "'12/31/2008'")

-Tom.


>I would like to call this stored procedure, but I am unable to pass
>parameters to the @Start and @End.
>
>Is thier a way to pass parameters to a pass through query from MS
>Access?
>
>SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],
>[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersAc tionID, 2)
> AS [Signed PP]
>FROM dbo.tblPersActionLog INNER JOIN
> dbo.tblPersActionHistory ON
>dbo.tblPersActionLog.PersActionID =
>dbo.tblPersActionHistory.PersActionID
>WHERE (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND
>(dbo.tblPersActionLog.Rejected = 0) AND
>(dbo.tblPersActionLog.IsPayAction = 0) AND
> (dbo.tblPersActionHistory.ActionTypeID = 5) AND
>(dbo.fn_IsParACorrection(dbo.tblPersActionHistory.PersActio nID) = 0)
>AND
>
>([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersActi onID) = 1)
>AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND
> (dbo.tblPersActionHistory.ItemDTG <= @EndDate)
>GROUP BY
>[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersAc tionID, 2)

Re: Pass through query from MS Access

gumby (gumbysolutions@cox.net) writes:
> I would like to call this stored procedure, but I am unable to pass
> parameters to the @Start and @End.
>
> Is thier a way to pass parameters to a pass through query from MS
> Access?

I really hope there is. Unfortunately, this is an SQL Server forum
and not an Access forum, so it's not the best place for advice.

As long as the queries are submitted through ADO you can use
..CreateParameter to specify the parameters. But I don't know if
what is meant with pass-through queries. (I have no experience of
Access myself.)

You are probably better off asking in an Access forum. But what I can
say from the SQL Server side of things is that you should never expand
parameter values directly into the query string, but always use
parameterised commands. There are three reasons for this:

1) If you expand the parameters, each new parameter values results in
a new cache entry, resulting in higher load on SQL Server for
compilation and memory.
2) Parameterised commands protects you against SQL injection, that is
a user entering data which affects the SQL syntax.
3) No problems with date values. For instance the snippet that
Tom van Stiphout will not work for French, German or British users.

So if pass-through queries in Access actually do not support parameters,
the answer is simple: don't use them.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx