Post reply

Access ADP Obtain value from stored procedure

  • September 27, 2005 at 5:06 pm

    #66906

    We have an Access 2000 ADP Database connecting to an SQL 2000 Server Database. We need to obtain output values from stored procedure to be able to manipulate the value within a module in Access. We could return a recordset and grab the value but is there an easier way to do it. Can someone please give us some advice.

  • September 28, 2005 at 6:40 am

    #593627

    Sure, here's a sample :

    Stored proc :

    CREATE PROCEDURE [dbo].[GetPkADP] @DBName as varchar(50), @ADPName as varchar(100), @PkADP as int output

    AS

    SET NOCOUNT ON

    SET @PkADP = (Select PkADP from dbo.ADPS A inner join dbo.Databases D on A.FkDB = D.PkDB where A.ADPName = @ADPName and D.DBName = @DBName)

    SET NOCOUNT OFF

    vb call :

    Private Function exec_GetPkADP(ByVal DBName As String, ByVal ADPName As String, ByRef PkADP As Integer, Optional ByRef ReturnValue As Integer) As Integer

    On Error GoTo Gestion

    Dim MyCmd As ADODB.Command

    Set MyCmd = New ADODB.Command

    MyCmd.CommandText = "dbo.GetPkADP"

    MyCmd.CommandType = adCmdStoredProc

    Dim MyParam As ADODB.Parameter

    Set MyParam = New ADODB.Parameter

    MyParam.Direction = adParamReturnValue

    MyParam.Name = "@Return"

    MyParam.Type = adInteger

    MyCmd.Parameters.Append MyParam

    Set MyParam = New ADODB.Parameter

    MyParam.Name = "@DBName"

    MyParam.Value = DBName

    MyParam.Size = 50

    MyParam.Direction = adParamInput

    MyParam.Type = adVarChar

    MyCmd.Parameters.Append MyParam

    Set MyParam = New ADODB.Parameter

    MyParam.Name = "@ADPName"

    MyParam.Value = ADPName

    MyParam.Size = 100

    MyParam.Direction = adParamInput

    MyParam.Type = adVarChar

    MyCmd.Parameters.Append MyParam

    Set MyParam = New ADODB.Parameter

    MyParam.Name = "@PkADP"

    MyParam.Value = PkADP

    MyParam.Size = 4

    MyParam.Direction = adParamInputOutput

    MyParam.Type = adInteger

    MyCmd.Parameters.Append MyParam

    MyCn.Open

    MyCmd.ActiveConnection = MyCn

    MyCmd.Execute exec_GetPkADP

    MyCn.Close

    ReturnValue = CInt(MyCmd.Parameters("@Return").Value)

    PkADP = MyCmd.Parameters("@PkADP").Value

    Set MyParam = Nothing

    Set MyCmd = Nothing

    Exit Function

    Gestion:

    ErrHandler ModuleName, Me.Name, "exec_GetPkADP", Err

    MsgBox Err.Description & " : " & Err.Number

    End Function

  • September 28, 2005 at 7:57 pm

    #593892

    Thanks

    How would we call this from access and what arguments would we need to put in.

  • September 29, 2005 at 6:55 am

    #594005

    Seriously

    put that code on the server

    Stored proc :

    create...

    vb call :

    put that cod ein a vb application an run it once. If you have questions, then come back and ask.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply