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.tblPe rsActionHistory .PersActionID) AS [Total Ct],
[dbo].fn_FindStartPa yPeriod(dbo.tbl PersActionHisto ry.PersActionID , 2)
AS [Signed PP]
FROM dbo.tblPersActi onLog INNER JOIN
dbo.tblPersActi onHistory ON
dbo.tblPersActi onLog.PersActio nID =
dbo.tblPersActi onHistory.PersA ctionID
WHERE (dbo.tblPersAct ionLog.StatusID BETWEEN 4 AND 7) AND
(dbo.tblPersAct ionLog.Rejected = 0) AND
(dbo.tblPersAct ionLog.IsPayAct ion = 0) AND
(dbo.tblPersAct ionHistory.Acti onTypeID = 5) AND
(dbo.fn_IsParAC orrection(dbo.t blPersActionHis tory.PersAction ID) = 0)
AND

([dbo].fn_ParNotExcep tion(dbo.tblPer sActionHistory. PersActionID) = 1)
AND (dbo.tblPersAct ionHistory.Item DTG >= @StartDate) AND
(dbo.tblPersAct ionHistory.Item DTG <= @EndDate)
GROUP BY
[dbo].fn_FindStartPa yPeriod(dbo.tbl PersActionHisto ry.PersActionID , 2)

[Tom van Stiphout]

Re: Pass through query from MS Access

On Wed, 2 Jan 2008 05:06:50 -0800 (PST), gumby
<gumbysolutions @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.tblPe rsActionHistory .PersActionID) AS [Total Ct],
>[dbo].fn_FindStartPa yPeriod(dbo.tbl PersActionHisto ry.PersActionID , 2)
AS [Signed PP]
>FROM dbo.tblPersActi onLog INNER JOIN
dbo.tblPersActi onHistory ON
>dbo.tblPersAct ionLog.PersActi onID =
>dbo.tblPersAct ionHistory.Pers ActionID
>WHERE (dbo.tblPersAct ionLog.StatusID BETWEEN 4 AND 7) AND
>(dbo.tblPersAc tionLog.Rejecte d = 0) AND
>(dbo.tblPersAc tionLog.IsPayAc tion = 0) AND
(dbo.tblPersAct ionHistory.Acti onTypeID = 5) AND
>(dbo.fn_IsParA Correction(dbo. tblPersActionHi story.PersActio nID) = 0)
>AND
>
>([dbo].fn_ParNotExcep tion(dbo.tblPer sActionHistory. PersActionID) = 1)
>AND (dbo.tblPersAct ionHistory.Item DTG >= @StartDate) AND
(dbo.tblPersAct ionHistory.Item DTG <= @EndDate)
>GROUP BY
>[dbo].fn_FindStartPa yPeriod(dbo.tbl PersActionHisto ry.PersActionID , 2)

[Erland Sommarskog]

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
..CreateParamet er 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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx