Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Washington D.C.

    Unanswered: Two Solutions: Which is betterRunning a Stored Procedure from ADO

    From one of my forms in an Access Project I want to run a Stored Procedure on the "After Insert" event of the form. The stored procedure is an Insert SQL Statement with two variables.
    What is the best way to pass the variables to the stored procedure and run it.

    Should I run a SQL Statement like

    srtSQL="EXEC SP_Insert (@Var1=Var1,@Var2=Var2)

    Or there is a better way to do it with ADO objects?


    I found two solutions. Which one do you think is the better one?
    The stored procedure on the Server:

    CREATE PROCEDURE SP_Insert_Into_CompanyAdrsContact
    @CompAdrsID int,
    @ContactID int
    Insert into tblCompanyAdrsContact (CompAdrsID,ContactID) Values (@CompAdrsID,@ContactID)

    On the Access form:

    1) Using a SQL stament dynamically to pass parameters from a form:

    strSQL = "Exec SP_Insert_Into_CompanyAdrsContact " & Me!CompAdrsID
    strSQL = strSQL & "," & Me!ContactID
    DoCmd.RunSQL (strSQL)

    2) Using ADO objects:
    Dim cmd As ADODB.Command
    Dim prmContactID As ADODB.Parameter
    Dim prmCompAdrsID As ADODB.Parameter

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "SP_Insert_Into_CompanyAdrsContact"

    Set prmCompAdrsID = cmd.CreateParameter("@CompAdrsID", adInteger, adParamInput)
    Set prmContactID = cmd.CreateParameter("@ContactID", adInteger, adParamInput)

    cmd.Parameters.Append prmCompAdrsID
    prmCompAdrsID.Value = Me![CompAdrsID]
    cmd.Parameters.Append prmContactID
    prmContactID.Value = Me![ContactID]


    Last edited by Sia; 03-07-03 at 16:33.

  2. #2
    Join Date
    May 2002

    Re: Two Solutions: Which is betterRunning a Stored Procedure from ADO

    If you are going to call this proc many times, it's best to use command parameter. This will allow you to gain some performance because of cache. If it's just a one time thing, just execute the string is fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts