I am trying to run a stored procedure in an Access Project against an SQL 
2000 db and get a single value back from it. This is my first time trying 
this and I don't think I have set it up right. When the code runs the 
error says that the procedure was expecting a parameter which was not 
supplied. But this is my ouput parameter so of course I can't supply it.
Where have I gone wrong?

Stored Procedure:
Alter Procedure "BOVAReturnIDfromCommonName"

@COMMON_NAME varchar(50),
@BOVAID char(6) OUTPUT
As


SELECT  @BOVAID =BOVA
FROM BOVA_TAXONOMY
WHERE COMMON_NAME = @COMMON_NAME


	/* set nocount on */
	return

Access code to get the value of @BOVAID out:

Private Sub cboCommonName_AfterUpdate()
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "BOVAReturnIDfromCommonName"

Dim prm1 As ADODB.Parameter
Set prm1 = cmd1.CreateParameter("@COMMON_NAME", adVarChar, adParamInput, 
50)
Dim prm2 As ADODB.Parameter
Set prm2 = cmd1.CreateParameter("BOVAID", adChar, adParamOutput, 6)


cmd1.Parameters.Append prm1
prm1.Value = Me.cboCommonName

cmd1.Execute

'Dim rst1 As ADODB.Recordset
'Set rst1 = New ADODB.Recordset

Dim strResult As String

strResult = cmd1("BOVAID")
Debug.Print strResult
End Sub

Try this..........
Build this function in a class

Public Function InstantRst(strSQL As String) As Recordset
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strConn As String
Set con = New ADODB.Connection
strConn = "Driver={SQL Server};" & _
          "Server=SQL_DEV;" & _
         "Database=SQL_TEST;" & _
          "UID=sa;PWD=password"
con.Open ConnectionString:=strConn
Set rst = New ADODB.Recordset
rst.Open strSQL, con, adOpenForwardOnly


 Set InstantRst = rst
'rst.Close
'Set rst = Nothing

End Function

Then in Access do this....

Dim rst1 As ADODB.Recordset
Dim strSQL As String
Dim strCOMMON_NAME As String

strSQL = "SELECT BOVA FROM BOVA_TAXONOMY WHERE COMMON_NAME = " &
strCOMMON_NAME

Set rst1 = myclass.InstantRst(strSQL)

Msgbox rst1!COMMON_NAME
----------------------------------------------------------------------------
-----------------------
I hope this helps,

Michael Sargent

-----Original Message-----
From: Kevin Gooss [mailto:gooss22@y...]
Sent: Wednesday, November 06, 2002 1:05 PM
To: ActiveX_Data_Objects
Subject: [activex_data_objects] Output Parameter from Access adp to SQL
Server 2000


I am trying to run a stored procedure in an Access Project against an SQL 
2000 db and get a single value back from it. This is my first time trying 
this and I don't think I have set it up right. When the code runs the 
error says that the procedure was expecting a parameter which was not 
supplied. But this is my ouput parameter so of course I can't supply it.
Where have I gone wrong?

Stored Procedure:
Alter Procedure "BOVAReturnIDfromCommonName"

@COMMON_NAME varchar(50),
@BOVAID char(6) OUTPUT
As


SELECT  @BOVAID =BOVA
FROM BOVA_TAXONOMY
WHERE COMMON_NAME = @COMMON_NAME


	/* set nocount on */
	return

Access code to get the value of @BOVAID out:

Private Sub cboCommonName_AfterUpdate()
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "BOVAReturnIDfromCommonName"

Dim prm1 As ADODB.Parameter
Set prm1 = cmd1.CreateParameter("@COMMON_NAME", adVarChar, adParamInput, 
50)
Dim prm2 As ADODB.Parameter
Set prm2 = cmd1.CreateParameter("BOVAID", adChar, adParamOutput, 6)


cmd1.Parameters.Append prm1
prm1.Value = Me.cboCommonName

cmd1.Execute

'Dim rst1 As ADODB.Recordset
'Set rst1 = New ADODB.Recordset

Dim strResult As String

strResult = cmd1("BOVAID")
Debug.Print strResult
End Sub

Michael,

I am very thankful to you and what you suggested does work. However, I am 
trying to get the hang of this stored procedure bunk and I am still 
interested in knowing what is wrong with my proc. Also aren't these stored 
kinds of procedures faster because they are already compiled?

Kevin,

You are very welcome. I would use your stored procedures to do delete,
update, insert statements.
I believe you find that is where you'll find your benefit in performance. In
retrieval of data you are not going to see much benefit that is really
apparent.

Here is a sample of an update......

Public Function Update_Pairs(intID As String, strPAIR_NUMBER As Variant,
strUNITATERM As String, strWIREMARKA As Variant, _
                             strUNITBTERM As String, strWIREMARKB As
Variant, strWIRE_COLOR As String, strWIRE_FUNCTION As String, _
                             intLINENO As Variant, intAPP_ID As Integer)
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim sConnect As String -"This is declared as a constant at the beginning of
the class"

sConnect = GetConnectString(DB_SERVER, DB_NAME, DB_USER_ID, DB_PASSWORD)

Set Cmd1 = New ADODB.Command
   ' Establish connection.
Set Conn1 = New ADODB.Connection
   Conn1.ConnectionString = sConnect
   Conn1.Open
' open command object
   Set Cmd1 = New ADODB.Command
   Cmd1.ActiveConnection = Conn1
   Cmd1.CommandText = "procUpd_Pairs"
   Cmd1.CommandType = adCmdStoredProc
        
Cmd1.Parameters("@ID") = intID
Cmd1.Parameters("@PAIR_NUMBER") = strPAIR_NUMBER
Cmd1.Parameters("@UNITATERM") = strUNITATERM
Cmd1.Parameters("@WIREMARKA") = strWIREMARKA
Cmd1.Parameters("@UNITBTERM") = strUNITBTERM
Cmd1.Parameters("@WIREMARKB") = strWIREMARKB
Cmd1.Parameters("@WIRE_COLOR") = strWIRE_COLOR
Cmd1.Parameters("@WIRE_FUNCTION") = strWIRE_FUNCTION
Cmd1.Parameters("@LINENO") = intLINENO
Cmd1.Parameters("@APP_ID") = intAPP_ID
Cmd1.Execute

Conn1.Close
Set Conn1 = Nothing
End Function

--------------------------------------
Stored Procedure on SQL Server-

CREATE PROCEDURE procUpd_Pairs (
@ID int = NULL ,
@PAIR_NUMBER varchar (12) = NULL ,
@UNITATERM varchar (20) = NULL ,
@WIREMARKA varchar (50) = NULL ,
@UNITBTERM varchar (20) = NULL ,
@WIREMARKB varchar (50) = NULL ,
@WIRE_COLOR varchar (11) = NULL ,
@WIRE_FUNCTION varchar (50) = NULL ,
@LINENO float =  NULL ,
@APP_ID int = NULL
 )
AS
SET NOCOUNT ON
UPDATE PAIRS
SET [PAIR NUMBER]= @PAIR_NUMBER, UNITATERM = @UNITATERM, WIREMARKA 
@WIREMARKA, 
UNITBTERM = @UNITBTERM, WIREMARKB = @WIREMARKB, [WIRE COLOR] = @WIRE_COLOR,
[WIRE FUNCTION] = @WIRE_FUNCTION, 
[LINENO]=@LINENO, APP_ID=@APP_ID
WHERE ID =@ID

GO
------------------------------------------------------
Best wishes,

Michael Sargent



-----Original Message-----
From: Kevin Gooss [mailto:gooss22@y...]
Sent: Wednesday, November 06, 2002 1:32 PM
To: ActiveX_Data_Objects
Subject: [activex_data_objects] RE: Output Parameter from Access adp to
SQ L Server 2000


Michael,

I am very thankful to you and what you suggested does work. However, I am 
trying to get the hang of this stored procedure bunk and I am still 
interested in knowing what is wrong with my proc. Also aren't these stored 
kinds of procedures faster because they are already compiled?

Hi Kevin,

I agree with you.   The stored procedure approach is better.  The problem 
with your original code is that although you created the output parameter, 
you didn't append it to the parameters collection. You must append it, you 
just don't give it a value, i.e. add "cmd1.Parameters.Append prm2" before 
you do the execute and it will work just fine.

hth
Phil
> Michael,

> I am very thankful to you and what you suggested does work. However, I 
am 
t> rying to get the hang of this stored procedure bunk and I am still 
i> nterested in knowing what is wrong with my proc. Also aren't these 
stored 
k> inds of procedures faster because they are already compiled?