Pass through query from MS Access

[gumby]

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.Pe rsActionID, 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. PersActionID) = 0)
AND

([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersA ctionID) = 1)
AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND
(dbo.tblPersActionHistory.ItemDTG <= @EndDate)
GROUP BY
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)

[Tom van Stiphout]

On Wed, 2 Jan 2008 05:06:50 -0800 (PST), gumby
<gu************@cox.netwrote:

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.Pe rsActionID, 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 .PersActionID) = 0)
AND

([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersA ctionID) = 1)
AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND
(dbo.tblPersActionHistory.ItemDTG <= @EndDate)
GROUP BY
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)

[Erland Sommarskog]

gumby (gu************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx