Hi!

Somebody could give me a sample code to execute an store procedure on a SQL
Server 2000 database from an MS-Access 2000?

The Store Procedure has a parameter, and I need to call it from a VBA
function

Thanks!

Hi Andrs,

You don't say if ADP or MDB

Example for ADP and ADO

Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter

With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "dbo.myStoredProc"
    .CommandType = adCmdStoredProc

    ' First a sample to bind a parameter for a
    ' return-value to the command-object
    Set Prm = cmd.CreateParameter("Return", adBigInt,
adParamReturnValue)
    cmd.Parameters.Appnd prm

    ' This sample is for binding a char-var as a
    ' parameter to the command-object
    Set prm = cmd.CreateParameter("MyVar", adVarChar, adParamInput,
Len('MyVar'), 'MyVar')
    cmd.Parameters.Append prm

    ' Now we fire the stored procedure
    .Execute

    ' ... and see, what is the return-value of the sp
    MsgBox .Parameters("Return")
End With

' Now destroy the objects
Set cmd = Nothing
Set prm = Nothing

HTH :-)

--
Gru?, Uwe Ricken
MCP FOR MS ACCESS DEVELOPMENT
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
____________________________________________________
APP:    http://www.AccessProfiPool.com
FAQ:   http://www.donkarl.com/AccessFAQ.htm

Sorry not right e-mail box leave me out of your next question, have not a
clue why I am getting these messages.

And how does this look like for an ".mdb"-Database?
I try to run a Stored Procedure on a SQL-Server 7 which returns a Resultset,
using one parameter: