SQL SP vs. Access Pass-Through query / Form
Posted on 2007-03-28
I recently migrated my Access BE to SQL 2k5. I am in the process of changing all of my Access queries to SQL views and SPs. I have accomplished a portion of the task with one of my queries. I created the SP in SQL w/ the parameter. I then created a SQL Pass-Through query in Access to execute the SP. All works well.
I also created a form that the user will use to select the parameter from a cmb. In this case, the parameter is a report month (mmm-yy). The question is, how do I change my SQL pass-through query to use the value from the form and not prompt the user?
Details below include: Form Details, SQL SP, Pass-Through Query
1. Form Details:
Form Name: frmSys_RunSPs
Cmb Name: cmbRptMonth (This contains the value i need to pass as parameter)
2. SQL Stored Procedure
ALTER PROCEDURE [dbo].[upOrders_ISP]
SELECT ord.ReportMonth, ord.OrderNum, ord.OrderDate,
jn.JobNum, ord.Customer, ord.Machine, ord.MachineSN,
ord.UnitType, ssn.SpindleSN, ord.OrderType,
ord.ServiceType, isp.ISPComments, isp.ISPType
FROM ((tblOrders ord LEFT JOIN tblOrders_JobNum jn
ON ord.OrderID = jn.OrderID) LEFT JOIN
tblOrders_SpindleSN ssn ON ord.OrderID = ssn.OrderID)
LEFT JOIN tblOrders_ISP isp ON ord.OrderID = isp.OrderID
WHERE ((ord.ReportMonth)= @MyParam1)
3. SQL Pass-Through Query
NOTE: Query properties are already set for server connection and 'Mar-07' needs to be changed to reference the value from frmSys_RunSPs
EXEC upOrders_ISP 'Mar-07'
Thanks for your help.