Link to home
Start Free TrialLog in
Avatar of adraughn
adraughnFlag for United States of America

asked on

SQL SP vs. Access Pass-Through query / Form

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]
(
@MyParam1 Varchar(50)
)
AS
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.

-adria
ASKER CERTIFIED SOLUTION
Avatar of rboyd56
rboyd56

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of adraughn

ASKER

I had to change it a little bit, but you rock...

thanks, enjoy the points.

Private Sub cmdOpenRpt_Click()
Dim strSQL As String
Dim stroldsql As String 'Added

stroldsql = ChangeSQL("qryOrders_ISP2", "execute upOrders_ISP " & "'" & _
    Forms![frmSys_RunSPs]![cmbRptMonth] & "'") 'Had to add double quotes
DoCmd.OpenQuery "qryOrders_ISP2" 'Added open query

End Sub