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
LVL 13
adraughnAsked:
Who is Participating?
 
rboyd56Connect With a Mentor Commented:
Not sure if this is what you are looking for but there may be a sample of what you are trying to do here:

http://groups.google.com/group/microsoft.public.access.queries/browse_thread/thread/88710345ee737757/5e2e248b896edd1e%235e2e248b896edd1e

Use code to change the SQL of the P-T query. You would pass in the name of
the P-T and the new SQL

strOldSQL = ChangeSQL("qsptMyPT","execute GetCustomerName " &
Forms![Customer_Frm]![txtCustomer])


Function ChangeSQL(strQuery as String, strSQL as String) as String
    Dim db As DAO.Database
    Dim qdf as DAO.QueryDef
    Set db = CurrentDb
    Set qdf = db.QueryDefs(strQuery)
    ChangeSQL = qdf.SQL
    qdf.SQL = strSQL
    Set qdf = Nothing
    Set db = Nothing
End Function


0
 
adraughnAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.