Using a form to populate dates to a Passthrough Query

[Timothy via AccessMonster.com]

I have an ACCESS 2003 passthrough query to a SQL server that works great. It
has the date ranges hard coded into it. I need to pass the dates from a form
into this query. I have seen several posts that seem to address this issue,
but I can't make them work. I am still relatively new to this, so any help
would be greatly appreciated. The SQL that is working is as follows:

Select ACT.transactionNumber AS [Transaction#], OM.outletNumber AS [Outlet#],
OM.outletName AS [Outlet Name], Convert(DATETIME,CONVERT(CHAR(10),ACTS.
dispatchDateTime,101)) AS [Dipsatch Date], ACT.assignedContactUserName AS
[Created By], UPPER(LTRIM(RTRIM(CCE.firstName))) As [First Name], UPPER(LTRIM
(RTRIM(CCE.lastName))) AS [Last Name], OM.locationMasterID

FROM CCE_ContactCtr_V3_Reporting.dbo.OUTLET_MASTER OM,
CCE_ContactCtr_V3_Reporting.dbo.ACTIVITY ACT, CCE_ContactCtr_V3_Reporting.dbo.
ACTIVITY_SUPPORT ACTS, CCE_ContactCtr_V3_Reporting.dbo.ACTIVITY_TYPE ACTT,
CCE_ContactCtr_V3_Reporting.dbo.CCEUSER CCE

WHERE OM.outletNumber = ACT.outletNumber AND ACT.transactionNumber =
ACTS.transactionNumber AND ACT.activityTypeValue = ACTT.activityTypeValue and
ACT.assignedContactUserName = CCE.userName AND ((ACT.closeDateTime between
{ts '2007-05-21 00:00:00'} and {ts '2007-06-03 00:00:00'}) AND (ACT.
activitySourceLD = 'S') AND (ACT.activityTypeValue = 'PMR'))

 

[Sylvain Lafontaine]

You cannot pass parameters to a passthrough query; instead, you must change
the sql string each time:

dim rst as DAO.recordset
dim qdf as DAO.QueryDef
Set qdf = currentdb.QueryDefs("qryExecuteMySP")
qdf.SQL = "exec MySP " & FirstParameter & ", " & SecondParameter .......
qdf.execute

The format that you are using for the datetime values are the full ODBC
standard format but SQL-Server is able to understand others, more simple
versions, too.

You should ask this kind of question in the m.p.access.odbcclientsvr
newsgroup as this one is about ADP and SQL-Server and not about ODBC linked
tables and passthrough queries.