Solved

SQL SP vs. Access Pass-Through query / Form

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to automate & schedule this Index optimization Script ? 10 83
SQL help 5 53
SQL Merige returns error code when updating 15 52
Get row count of current SQL query 8 54
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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…

816 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

11 Experts available now in Live!

Get 1:1 Help Now