Solved

SQL SP vs. Access Pass-Through query / Form

Posted on 2007-03-28
2
329 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:adraughn
2 Comments
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18811869
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
 
LVL 13

Author Comment

by:adraughn
ID: 18812107
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now