Output parameter from SQL Server to Access-97 form 2

[Avacha]

1. If any one knows how to retrieve output parameter from SQL Server Stored Procedure to Access-97 form.
2. How to coach Rrecordset Absolute position (data base SQL Server. Front end Access-97 from).
Thanks.

 

[bustell]

As the last thing you do in the Stored Procedure, return the parameter as a query: SELECT @YourParameter AS YourParamter

Then use a pass-through query or make an ADO connection to run the stored procedure. It will return a recordset with the parameter.
Pat B

 

[cmmrfrds]

Here is an Example from an Access Form along with the stored procedure used in the example. This example is doing several things including an output parameter. It is from an Access Project so the connection string is already established.

Option Compare Database
Option Explicit
Public supplyID As Integer
'-- Reference library is "Microsoft ActiveX Data Objects 2.6 Library"
Private Sub Form_Open(Cancel As Integer)
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset, rst2 As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter, param0 As Parameter
supplyID = 111

On Error GoTo ErrHandler
' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn

'----- Return code Parm
Set param0 = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append param0
param0.Value = 0
'----- Input Parm
Set param1 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = supplyID
'---- Output Parm
Set param2 = cmd.CreateParameter("Output", adVarWChar, adParamInputOutput, 20)
cmd.Parameters.Append param2
param2.Value = ""

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_supplier"
cmd.CommandType = adCmdStoredProc

Dim recAffected As Integer '- available on insert or update
Set rst = cmd.Execute(recAffected)

Debug.Print "Records Affected = "; recAffected

Debug.Print "first parm = "; cmd.Parameters.Item(0).Value
Debug.Print "second parm = "; cmd.Parameters.Item(1).Value
Debug.Print "third parm = "; cmd.Parameters.Item(2).Value

Dim yy As Form
Set yy = Forms!frm_supplier.Form
Set yy.Recordset = rst

Dim cnt As Integer
cnt = 0
While (rst.state = adStateOpen)
Debug.Print "value of rst = "; rst(cnt)
Debug.Print "the Record state = "; rst.state
Debug.Print "the first field = "; rst.Fields(1)

Set rst = rst.NextRecordset(cnt)
cnt = cnt + 1
Wend

Exit Sub
ErrHandler:
Dim er As ADODB.Error
For Each er In cnn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.Description
Debug.Print "err source = "; Err.Source
Next
End Sub

'STORED PROCEDURE
Alter Procedure "sp_supplier"
(
@supplyid as int,
@supply varchar(20) OUTPUT
)
As
-- The -- is used for commenting in SQL Server or /* comment */
--set nocount on

--insert into customer (custnum, fname, lname,phone,areacode,postcode,state,city,address)
-- Values(553444551, 'Jerry','LastName','7774444','208','888','MN','Duluth','12 sam lane')

select suplrnum, name, address, state from supplier

select count(*) as cnt, state
from supplier
where state is not null
group by state

select @supply = 'my message out'

return 99 -- my return code

 

[bustell]

Sorry, that is going a little deeper into ADO that I have ever needed. I will try to look into it this weekend. Hopefully someone can help you out sooner. Good Luck.
Pat B

 

[jimmythegeek]

Here is a more condensed version using a stored procedure with one output parameter, and no input parameters. The first connection string uses SQL Server security where you have to supply a user and password, the second uses integrated security which uses the user's NT or 2000 login. You can use whichever you prefer, but use only one.

Change everything in red appropriately:

==========
Private Const strCN As String = "Provider=SQLOLEDB.1;Persist Security Info=false;User ID=username;Password=password;Initial Catalog=databasename;Data Source=servername"

OR

Private Const strCN As String = "Provider=SQLOLEDB.1;Persist Security Info=true;Initial Catalog=databasename;Data Source=servername"

==========
Private Sub GetOutPutParam()
Dim adoCN As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoPRM As ADODB.Parameter
Dim myVar As String

Set adoCN = New ADODB.Connection
adoCN.Open strCN

Set adoCMD = New ADODB.Command
With adoCMD
.ActiveConnection = adoCN
.CommandType = adCmdStoredProc
.CommandText = "StoredProcedureNameHere"
Set adoPRM = .CreateParameter("ReturnParam", adVarChar, 50, adParamOutput)
.Parameters.Append adoPRM
.Execute

myVar = .Parameters.Item("ReturnParam&quot
End With

Set adoPRM = Nothing
Set adoCMD = Nothing
Set adoCN = Nothing
End Sub
==========

This example uses a string variable (myVar) in the code and in the parameter. Now myVar is set to the output parameter, and you can do whatever you want with it. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[Avacha]

<b>Thank you every one!</b> <img

http://www.mysite.com/images/happy.gif>