Pass parameter to stored procedure

A Stored Procedure (usp_ActivityMaster) with a parameter of '@ActivityDate'

I built a form with txtActivityDate and command button to pass the value of
txtActivityDate to @ActivityDate in the sproc.

I have a pass-through query (qsptActivityMaster) that successfully runs:
exec usp_ActivityMaster @ActivityDate = '07/07/2009'

What should the OnClick event be for the command button in order to pass
txtActivityDate to @ActivityDate?

Is this how YOU would achieve this?

Thanks!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

Do you have a pass-througth query handy?

I ask the above Since when I build applications that use link tables to SQL
server, then what I do as they create one pass through query that I use for
all of my calls the stored procedures. assuming you've got one pass through
query, then you can use the following simple code:

dim rst as dao.RecordSet
set rst = currentdb.QueryDefs("MyPassThought").Execute

If you want to execute a stored procedure of *your* choice at runtime, NOT
as a saved query, then again assuming you have that "one" catch all query
saved as pass-though, you go:


Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset


Set qdfPass = CurrentDb.QueryDefs("MyPass")
qdfPass.SQL = "exec sp_myProc"
qdfPass.Execute

For a paramter, then use:

qdfPass.SQL = "exec sp_myProc " & myDate

You should however format date to interntnatn, or at least:

qdfPass.SQL = "exec sp_myProc " & format(myDate,"mm\/dd\/yyyy")


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Albert,

Thank you again for your input.

This time, though, your response is a bit over my head. You seam to be asking
questions for answers I already gave in my original post. Then you gave an
answer that wasn't for my question.

I appreciate the inteligence you provide and would like to adapt some of your
practices because I find them to make developing much simpler. So the concept
of using a single pass-through query is very interesting to me.

But, for my immediate need I have multiple pass through queries. The one for
which I need a solution is the one that requires a parameter to be passed at
the time of execution. Please re-read my question and, if you understand it
and can provide the necessary solution, please respond.

I try to provide all the necessary information in my original post to avoid
wasting the time of generous people like you. If I was unclear or left
something out of my description, I am so sorry. I didn't mean to. Reading it
again myself, I believe it looks pretty clear.

Please let me know where you need more definition.

Thanks!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:98e05618ab260@uwe...
Albert,

Thank you again for your input.

This time, though, your response is a bit over my head. You seam to be
asking
questions for answers I already gave in my original post. Then you gave an
answer that wasn't for my question.

The answer I gave is:

qdfPass.SQL = "exec sp_myProc " & format(myDate,"mm\/dd\/yyyy")

Presumably, you would use the above and change "myDate" to your text box
(I kind thought that was of obvious).

So, change myDate to whatever the name of the date value is. I think in your
case that date is txtActivityDate.

eg:

qdfPass.SQL = "exec sp_myProc " & format(me.txtActivityDate,"mm\/dd\/yyyy")

In teh above "example" I used sp_myProc for the stored procedure name.
Again, it is assumed that your change the sp name to your needs.
(you just have to start and learn how to read sample code and how to use
that code for your own needs - I don't have any "secrect" suggestion in this
regards ).

So, based on the above example, then we get:

qdfPass.SQL = "exec usp_ActivityMaster" & _
format(me.txtActivityDate,"mm\/dd\/yyyy")

So, I will quote again from my suggestion:

quote
If you want to execute a stored procedure of *your* choice at runtime, NOT
as a saved query, then again assuming you have that "one" catch all query
saved as pass-though, you can go:
/quote

So, the above is only a suggestion, but it's also a very typical approach
used by most developers. So either way I'm saying gosh, it's probably likely
that you already have some pass-though queries already built and handy here,
so lets use one of those to solve this problem with VERY little code.

On the other hand, I suppose you could build ONE query that is pass-through
with the name of the stored procedure. However that going to be quite
un-flexible.

Also keep in mind, that if the stored proc is not designed (does not) to
return records, but just does local processing (run code) on the sql server
side, then you have to open up that pass through query property sheet and
set the "return records" = no. Of course if the stored procedure "can"
return records, then leave the typical defaulting of returns records = yes
for that pass-though query.

Note that the none of the above suggestion(s) will work if you're using an
access ADP project.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Based on what I understand of what you told me, this is what I have:

Private Sub cmdRun_Click()
On Error GoTo Err_cmdRun_Click
Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdfPass = CurrentDb.QueryDefs("qsptActivityMaster")
qdfPass.SQL = "exec usp_ComTrakActivityMaster @ActivityDate=" & Format(Me.
txtActivityDate, "mm/dd/yyyy")
qdfPass.Execute

Exit_cmdRun_Click:
Exit Sub

Err_cmdRun_Click:
MsgBox Err.Description
Resume Exit_cmdRun_Click
End Sub

This is what I recieve:
"Cannot execute a select query."

If any of your instructions were to modify qsptActivityMaster (the pass-
through query) I didn't understand the instruction. However, I don't believe
it should contain a hard coded date. This is the statement in
qsptActivityMaster:

exec usp_ComTrakActivityMaster @ActivityDate = '7/11/2009'

What my original post did not include was that I desire to open the results
of this process into a report "rptActivityMaster". Currently, I manually edit
the date of the above statement and open the report using
"qsptActivityMaster" as the Record Source.

Thanks for your help!

--
---
TraciAnn

Message posted via http://www.accessmonster.com

What you're showing does modify qsptActivityMaster: it changes the actual
SQL of the pass-through query.

Note, though, that your code would appear to be incorrect: you're not
putting the single quotes around the date.

You need either

qdfPass.SQL = "exec usp_ComTrakActivityMaster " & _
"@ActivityDate='" & Format(Me.txtActivityDate, "mm/dd/yyyy") & "'"

or

qdfPass.SQL = "exec usp_ComTrakActivityMaster " & _
"@ActivityDate=" & Format(Me.txtActivityDate, "\'mm/dd/yyyy\'")

Exagerated for clarity, those are


qdfPass.SQL = "exec usp_ComTrakActivityMaster " & _
"@ActivityDate= ' " & Format(Me.txtActivityDate, "mm/dd/yyyy") & " ' "

and

qdfPass.SQL = "exec usp_ComTrakActivityMaster " & _
"@ActivityDate=" & Format(Me.txtActivityDate, "\ ' mm/dd/yyyy\ ' ")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:9902ed57df2ca@uwe...
Based on what I understand of what you told me, this is what I have:

Private Sub cmdRun_Click()
On Error GoTo Err_cmdRun_Click
Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdfPass = CurrentDb.QueryDefs("qsptActivityMaster")
qdfPass.SQL = "exec usp_ComTrakActivityMaster @ActivityDate=" &
Format(Me.
txtActivityDate, "mm/dd/yyyy")
qdfPass.Execute

Exit_cmdRun_Click:
Exit Sub

Err_cmdRun_Click:
MsgBox Err.Description
Resume Exit_cmdRun_Click
End Sub

This is what I recieve:
"Cannot execute a select query."

If any of your instructions were to modify qsptActivityMaster (the pass-
through query) I didn't understand the instruction. However, I don't
believe
it should contain a hard coded date. This is the statement in
qsptActivityMaster:

exec usp_ComTrakActivityMaster @ActivityDate = '7/11/2009'

What my original post did not include was that I desire to open the
results
of this process into a report "rptActivityMaster". Currently, I manually
edit
the date of the above statement and open the report using
"qsptActivityMaster" as the Record Source.

Thanks for your help!

--
---
TraciAnn

Message posted via http://www.accessmonster.com

Douglas,

Thank you for your assistance!

What you're showing does modify qsptActivityMaster: it changes the actual
SQL of the pass-through query.

For educational purposes, are you saying that it does not matter what is in
qsptActivityMaster, because the code will replace the SQL with the SQL in the
code?

You need either

qdfPass.SQL = "exec usp_ComTrakActivityMaster " & _
"@ActivityDate='" & Format(Me.txtActivityDate, "mm/dd/yyyy") & "'"
...

Is this the only incorrect code? Making the syntax correction for the single
quotes (as provided above) I am still receiving the same error - "Cannot
execute a select query."

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:990472c4126d7@uwe...
What you're showing does modify qsptActivityMaster: it changes the actual
SQL of the pass-through query.

For educational purposes, are you saying that it does not matter what is
in
qsptActivityMaster, because the code will replace the SQL with the SQL in
the
code?

Correct

You need either

qdfPass.SQL = "exec usp_ComTrakActivityMaster " & _
"@ActivityDate='" & Format(Me.txtActivityDate, "mm/dd/yyyy") & "'"
...

Is this the only incorrect code? Making the syntax correction for the
single
quotes (as provided above) I am still receiving the same error - "Cannot
execute a select query."

Is qsptActivityMaster correctly set up as a pass-through query? Does the
stored procedure work properly in SQL Server?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Is qsptActivityMaster correctly set up as a pass-through query?

It is saved as a pass-through query. With a valid Connect String.

The syntax of the query is currently:
exec usp_ComTrakActivityMaster @ActivityDate='07/10/2009'

Does the stored procedure work properly in SQL Server?

When I open qsptActivityMaster with the above command line it gives the
expected recordset.

Thanks!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

After your code resets the SQL of qsptActivityMaster, what is the SQL?

Does it run correctly with that modified SQL?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:990557dd9eabd@uwe...
Is qsptActivityMaster correctly set up as a pass-through query?

It is saved as a pass-through query. With a valid Connect String.

The syntax of the query is currently:
exec usp_ComTrakActivityMaster @ActivityDate='07/10/2009'

Does the stored procedure work properly in SQL Server?

When I open qsptActivityMaster with the above command line it gives the
expected recordset.

Thanks!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1