How do I execute a stored procedure

[Jeff Vila via AccessMonster.com]

I have an Access Data Project (Access 2000) that links up to a SQL Server
on the backend. I have a stored procedure on the SQL server that accepts
two paramaters and then executes an update command. My question is how do I
reference the stored procedure and pass it the two paramaters in my Visual
Basic code. From what I understand I have to use ADO and create a public
subroutine and then call that routine when I want to execute the stored
procedure. Is there an easier method???? Any help is appeciated. Thanks,

 

[Alex White MCDBA MCSE]

Hi Jeff,

passing 2 values to the stored procedure called myStoredProcedureName, this
is the quick and dirty way, you may want to setup a ADO connection and run
it from there.

CurrentProject.Connection.Execute "myStoredProcedureName " &
Me.Religion_ID.Value & ", 'Early'"

A tip, if you have a control on the form called pmyID and you have a
parameter within the stored procedure call @pmyID you should not have to
specify the parameter when calling the stored procedure from the form, as it
will be passed automatically into the stored procedure.

 

[Jeff Vila via AccessMonster.com]

Thanks, I got it working. On a seperate issue, I'm trying to use the
ApplyFilter method to filter my main form but it's saying "cannot apply
filter on one or more of the fields specified in the filter property".
Any ideas? My codes is:

DoCmd.ApplyFilter , "Cases.[ID]=[Search for ID = ]"

 

[Alex White MCDBA MCSE]

Hi Jeff,

Personally I never use filters, I use the recordsource and send select
statements to it and refresh/requery the form. I like the idea that the
smallest amount of data is going to the form, with the filtering the normal
method is show everything and then let me filter from there.

But in the example below have you tried

DoCmd.ApplyFilter , "Cases.ID=1"

to see what happens

 

[Jeff Vila via AccessMonster.com]

Thanks again Alex,

I got this to work:

IDCheck = InputBox("Please enter the Case ID you wish to search for.",
"Search")

DoCmd.ApplyFilter , "Cases.ID=" & IDCheck