Solved

SQL SP vs. Access Pass-Through query / Form

Posted on 2007-03-28
2
333 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL joins 9 56
Pivot Query Problem 9 49
Error when saving to sql table a '/' 5 31
SSRS: Why is Visual Studio stripping these properties? 2 39
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 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