Re: Passing Input Parameter

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Graham R Seach (gseach_at_NOSPAMpacificdb.com.au)
Date: 05/24/04

  • Next message: Andro Dgebuadze: "Re: Passing Input Parameter" 
    Date: Tue, 25 May 2021 09:42:07 +1000

    Andro,

    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command

    Set cn = New ADODB.Connection
    Set cmd = New ADODB.Command

    cn.Open strConnectionString
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "COA to Table1"
        .Parameters.Refresh
        .Parameters("@RowID") = 77
        .Execute
    End With

    cn.Close
    Set cmd = Nothing
    Set cn = Nothing

    Regards,
    Graham R Seach
    Microsoft Access MVP
    Sydney, Australia

    Microsoft Access 2003 VBA Programmer's Reference
    http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

    "Andro Dgebuadze" <andro@gboc.ge> wrote in message
    news:%23TrieIYQEHA.3264@TK2MSFTNGP10.phx.gbl...
    > Hi
    >
    > I am new to ADP and have trivial question:
    > How to pass a parameter to a stored procedure?
    >
    > Here is my SP:
    >
    > ===================================
    > CREATE PROCEDURE dbo.[COA to Table1]
    > @RowID INT
    > AS INSERT INTO dbo.Table1
    > (Seg2)
    > SELECT Seg2
    > FROM dbo.COA
    > WHERE (ID = @RowID)
    > GO
    > ====================================
    > If I run this this procedure it asks me RowID parameter and when I submit
    a
    > number it appents 1 record to Table 1.
    >
    > But how should I do it by Code? I tried DoCmd.OpenStoredProcedure but it
    has
    > no option to pass paramater.
    > I have tried through ADODB.Command but Execute command was failing.
    >
    > Can you give me a VBA Script to run this procedure with parameter RowID=77
    > (for example?)
    >
    > Thanks
    >
    > Andro
    >
    >

  • Next message: Andro Dgebuadze: "Re: Passing Input Parameter"

    Relevant Pages

    • Re: Dcount in ADP
      ... Dim cmd As ADODB.Command ... Set cmd = New ADODB.Command ... @ intItemAS Integer OUTPUT ... codes still work in ADP, but I'd like to make it work faster. ...
      (microsoft.public.access.formscoding)
    • Re: adLongVarChar parameter for TEXT data type size problem
      ... Dim cmd As ADODB.Command ... Set cmd = New ADODB.Command ... datatype. ... line below needs to be adLongVarChar to accomodate the backend's TEXT ...
      (microsoft.public.access.adp.sqlserver)
    • Re: New Record
      ... Dim cmd As ADODB.Recordset ... Set cmd = New ADODB.Recordset ... Dim EMP As String ... You're not addressing the subform properly. ...
      (microsoft.public.access.formscoding)
    • Re: adLongVarChar parameter for TEXT data type size problem
      ... Sub ListOfParameters (ProcedureName As String) ... Dim cmd As ADODB.Command ... Set cmd = New ADODB.Command ... line below needs to be adLongVarChar to accomodate the backend's TEXT ...
      (microsoft.public.access.adp.sqlserver)
    • New Record
      ... Dim cmd As ADODB.Recordset ... Set cmd = New ADODB.Recordset ... Dim EMP As String ... with nothing but an SSN. ...
      (microsoft.public.access.formscoding)