SQL Pass Through Query

[Emmitska]

Hello,

I have the following pass through query:
exec dbo.CDEPress_PointInTime @DistrictCode = '67439', @Date = '2004-12-19'

CDEPress_PointInTime is the name of the stored procedure.
The users are not Access litterate and I would like to know if that would be possible to create a form so they can enter a new DistrictCode (Date would be always the same...)? Once they submit the form, the value would be transmitted to this SQL Server query and executed.

Thank you very much,

Emilie.

 

[Emmitska]

I guess this should be my last concern. With these lines:

'set the object "qd" to the pass-through query
Set qd = db.QueryDefs("qsptYourPT")
'build the new sql string
strSQL = "exec dbo.CDEPress_PointInTime @DistrictCode = '" _
& Me.cboDistrictCode & "', @Date = '2004-12-19'"

If "qsptYourPT" is my path through query, why do I need strSQl then? Or maybe I do not understand quite well... Why do I need to reference my path through query since what is inside is exactly the exec...? Except that in the path through query (which is saved a a regular query, right?), I have my two parameters already filled in. I wuold symply like to execute strSQL.

 

[dhookom]

Emmitska,
Your original post suggested that you wanted users to be able to enter/select a value on a form that gets placed in a pass-through query without having to open the query in design view. That is exactly an only what my code does. It replaces the "saved" sql query (EXEC ....) with a new sql query that includes the district code they had selected on the form.

The code that I provided only changes the sql. It is similar to someone opening the query in design view, changing the sql, and closing the query. If you want the query displayed, you will have to open it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Emmitska]

How do I open it in VBA?

 

[PHV]

Take a look at the DoCmd.OpenQuery method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Emmitska]

dhookom and PHV, thank you so much!
I cannot beleive I spent a week on less than ten lines of code...

It worked perfect!

Thanks for your help.