Capturing value from stored procedure

[Jonathan Blitz]

I need to call an SQL Server stored procedure and return the value to
Access.
I tried using a connection.execute command and assigning the result to a
recordset but I seem to get nothing.

The stored procedured definetly works, so where is the problem?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[Alick [MSFT]]

Hi Jonathan,

We can try ADODB.Command object to execute the stored procedure on the SQL
Server; here is a sample, the sample execute the sp and return the result
set to recordset object.

Note: add reference to Microsoft ActiveX Data Object 2.x Library.

Option Compare Database
Option Explicit

Private Sub Command0_Click()

Dim objCmd As New ADODB.Command
Dim rs As New ADODB.Recordset

With objCmd

Set .ActiveConnection = CurrentProject.Connection

'Name of the Stored Procedure
.CommandText = "mystoredpro"
.CommandType = adCmdStoredProc

Set rs = .Execute

End With

rs.Close

Set objCmd = Nothing

End Sub

Stored Procedure script
=============
CREATE PROCEDURE mystoredpro
AS
select firstname
from Employees
GO

Please feel free to reply to the threads if you have any questions or
concerns.




Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: "Jonathan Blitz" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.formscoding
|
| I need to call an SQL Server stored procedure and return the value to
| Access.
| I tried using a connection.execute command and assigning the result to a
| recordset but I seem to get nothing.
|
| The stored procedured definetly works, so where is the problem?
|
| --
| Jonathan Blitz
| AnyKey Limited
| Israel
|
| "When things seem bad
| Don't worry and shout
| Just count up the times
| Things have worked themselves out."
|
|
|