call a stored procedure and pass parameters from access

I have a stored procedure that will add records to a table based on some parameters.  I have an access form that I would like to use to pass values to the stored procedure.  The form captures the data and then a button on the form with an event procedure should call the stored procedure and pass the values from the form.  The fields on the form are: CohortID, ProtocolID, DueDate, CohortActionDate, and CohorMemberID.  I am not familiar with the VBA code needed to accomplish this in access.  Can anyone share a step-by-step instruction on how to accomplish this?

Here is the stored procedure:

USE [Living_Whole]
GO
/****** Object:  StoredProcedure [dbo].[InsertMemberProtocolTasks]    Script Date: 05/01/2015 09:21:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            Alberto Melenciano
-- Create date:       4-22-2015
-- Description:      Insert CohortProtocol, CohortProtocolTiming and CohortProtocolTimingFulfillment (specific protocols)
-- =============================================
ALTER PROCEDURE [dbo].[InsertMemberProtocolTasks]
      -- Add the parameters for the stored procedure here
      @CohortID as int,
      @SetActivation as int,
      @ProtocolID as int,
      @CohortActionDate as smalldatetime,
      @DueDate as smalldatetime,
      @CohortMemberID as int
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      set @SetActivation = 0 -- 0 = Insert Protocol

if @SetActivation = 0
begin
--      begin transaction
      insert into cohortprotocol
      select
            @ProtocolID,
            com.CohortID,
            com.CohortMemberID,
            @CohortActionDate,
            @CohortActionDate as enteredDate
      from
            CohortMember com
      left join
            CohortProtocol cop
      on
            com.CohortMemberID = cop.CohortMemberID
            and
            com.CohortID = cop.CohortID
            and
            cop.protocolid = @protocolid      
      where
            com.cohortid = @cohortid
            and
            cop.CohortProtocolID is null
            and
            com.CohortMemberID = @CohortMemberID

      insert into CohortProtocolTiming      
      select
            CohortProtocolID,
            @DueDate,
            @CohortActionDate as enteredDate
      from
            CohortProtocol
      where
            cohortid = @cohortid
            and
            CohortActionDate = @CohortActionDate
--rollback
--commit

end;            

if @ProtocolID in (7,9,10,11,13)
begin
insert into CohortProtocolTimingFulfillment (CohortProtocolID, FullfillmentDate)
select
      CohortProtocolID,
      @DueDate
from
      CohortProtocolTiming
where
      entereddate = @CohortActionDate
      and
      CohortProtocolID in (select CohortProtocolID from CohortProtocol where cohortid = @cohortid and ProtocolID = @ProtocolID)
      and
      duedate = @DueDate
end;

END
AmelenciAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can do it like this:

Currentdb.Execute "InsertMemberProtocolTasks " & Me.CohortID & "," & me.SetActivation & "," & etc etc

The order of the parameters must match the order in which they're listed in the SP, and you must be sure to pass in properly formatted values - for example, for a Date, you would pass in the value as YYYY-MM-DD.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
I create passthrough queries in the QBE, and then change the SQL in the code.
Passthrough queries must use T-SQL syntax and formats
This executes spMyInspectors with the parameter value of 88038
exec spMyInspectors  88038
This executes spbreakdownbyinspector with three parameters, @startdate,@enddate, @inspectorid
Exec spbreakdownbyinspector '01-Sep-14' ,'30-Sep-14 11:59 PM' ,3

Once you have used the query editor to create a working passthrough, alteration is simple

Dim SQL as string
Dim QDF as QueryDef
SQL = "Exec spbreakdownbyinspector '01-Sep-14' ,'30-Sep-14 11:59 PM' ,3"
set qdf = CurrentDb.QueryDefs("MyPassthrough")
qdf.SQL = SQL
qdf.execute dbFailOnError

Note the Date literals with single quotes.
T-SQL wildcards and all other syntax are required.
@Scott's is the same idea, differing format.
He's getting the db to do the work without saving any objects -- or gaining any optimizations that the engines could do.
I've got an object in place, the querydef, which may or may not help performance.
But in the end, its executing  a valid T-SQL statement either way.
0
AmelenciAuthor Commented:
That seems easy enough.  I'll give a try.  Thank you Scott.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
gaining any optimizations that the engines could do.
Given that a Stored Proc will execute on the server, I don't really see where your method could possibly provide any sort of optimization gains over the methods I suggest.
0
Nick67Commented:
I don't say that I know it does; I've never done the experiment.
However provide any sort of optimization gains
The saved query has the connection string in it, and after it's used the once, Access can know that it's going to be passing a stored procedure to SQL Server, and it can know what SQL Server is going to return, and there's an object to conceivably hang all that information from.
A bare execution string in VBA has none of that.

I don't do action queries, so I couldn't really test well
In the following code block for a Select sproc, I tested a Docmd.OpenQuery vs a DAO qdf.OpenRecordset vs an ADO recordset.  The first two clock in ~1/2 second.  The last option is twice as fast.

Dim SQL As String
Dim QDF As QueryDef
Dim dbCon As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim rst1 As DAO.Recordset
Dim Duration(4) As Double



Duration(0) = Timer
DoCmd.OpenQuery "pthrSpBreakdownByInspector", acViewNormal, acReadOnly
Duration(1) = Timer
Duration(0) = Duration(1) - Duration(0)

Duration(1) = Timer
SQL = "Exec spbreakdownbyinspector '01-Sep-14' ,'30-Sep-14 11:59 PM' ,3"
Set QDF = CurrentDb.QueryDefs("pthrSpBreakdownByInspector")
QDF.SQL = SQL
Set rst1 = QDF.OpenRecordset(dbOpenDynaset, dbSeeChanges)
Duration(2) = Timer
Duration(1) = Duration(2) - Duration(1)

Duration(2) = Timer
dbCon.ConnectionString = Forms!tblDummy!ADOConnString.Value '"Provider=SQLNCLI11;SERVER=ITADMIN1\SQLEXPRESS;DATABASE=TI_Data;Integrated Security=SSPI"
dbCon.Open
dbCon.CursorLocation = adUseClient
With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "spbreakdownbyinspector"
    .Parameters.Append .CreateParameter("@StartDate", adDBDate, adParamInput, , #9/1/2014#)
    .Parameters.Append .CreateParameter("@EndDate", adDBDate, adParamInput, , #9/30/2014 11:59:00 PM#)
    .Parameters.Append .CreateParameter("@InspectorID", adInteger, adParamInput, , 3)
    .ActiveConnection = dbCon
    .NamedParameters = True
    Set rst = .Execute
End With

dbCon.Close
Set dbCon = Nothing
Set cmd = Nothing
Set rst = Nothing

Duration(3) = Timer
Duration(2) = Duration(3) - Duration(2)

MsgBox Duration(0)
MsgBox Duration(1)
MsgBox Duration(2)

Open in new window


Your mileage will undoubtedly vary, but its worth thinking about.

Nick67
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The saved query has the connection string in it, and after it's used the once, Access can know that it's going to be passing a stored procedure to SQL Server, and it can know what SQL Server is going to return, and there's an object to conceivably hang all that information from.
But modifying the query will force Access to throw out that execution plan, it would seem, so I think you'd be back to square one. I don't know the under-the-hood mechanics of the Access engine, so I certainly could not say for sure, but it makes sense (at least to me).

I generally use the Command syntax in your example (the 3rd type). That seems to be easiest to troubleshoot and modify, at least to me.
0
Nick67Commented:
I hadn't use the ADO syntax much because MS's guidance is very much DAO-centric
But some operations were a full order of magnitude faster (10x)
So I do have some of that in production now.
0
BitsqueezerCommented:
Hi Nick & Scott,

it's no wonder why the third variant is the fastest - in the first two you use DAO and the last one ADO and DAO means: JET/ACE in between, ADO not. Moreover the connection was made with SQLNCLI which is of course that fastest driver to be used for ADO (it only unfortunately doesn't create updatable recordsets for an Access form, I wrote about that here a few minutes ago:
http://www.experts-exchange.com/questions/28665687/Converting-Bits-to-INT.html#a40755507

Changing the SQL property of a UDF is of course useless as you have no advantage of a saved query anymore, you can then also use dynamic SQL, regarding performance (as Scott already said above). And a stored procedure of course saves the execution plan only on the server so nothing in (any) frontend influence the performance of the SP. For Access there is no need to create an execution plan for Pass-Through-Queries or if, then it would be always the same: Performing the execute command.
He's getting the db to do the work without saving any objects -- or gaining any optimizations that the engines could do.
I've got an object in place, the querydef, which may or may not help performance.
It simply doesn't help in case of execution of a SP. The command is always "execute", no table/view needs to be analyzed for a better execution plan. The only small advantage is that you can add parameters with the parameter property which makes it harder for SQL injection. But that's the same with executing it directly with ADO and it's parameter collection.

And to the author: Instead of using a SP which changes the data (which would mean that you need to use an unbound form) try to use the method I described in the link above, then you have an updatable form based on a SP. Any inserts into other tables which the SP does can then be done with a trigger on the server.

Cheers,

Christian
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.