Microsoft KB Archive/128408

= ACC: How to Return Values from SQL Stored Procedures =

Article ID: 128408

Article Last Modified on 11/17/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q128408



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes how to use SQL pass-through queries to retrieve variables from SQL Server stored procedures.

This article assumes that you are familiar with Microsoft SQL Server. It also assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access version 7.0) is called Access Basic in version 2.0.



MORE INFORMATION
In order to retrieve a value from a SQL Server stored procedure, you must design the stored procedure so that it returns values. For example, in ISQL /W (a Microsoft SQL Server utility), you can create the following stored procedure:

  CREATE PROCEDURE TEST AS

Declare @Title varchar(50) declare @Title2 varchar(40)

select @Title = 'this is a test' select @Title2 = 'this is a test2'

select x=@Title, y=@Title2

To run this stored procedure in Microsoft SQL Server, type the following command in ISQL /W:

Exec TEST

It returns two columns named x and y that represent the variables @Title and @Title2. In order to return these values to Microsoft Access, you need to create a recordset in a SQL pass-through query. To do so, create the following function in a new module:

Function SP_Value Dim mydb As Database Dim myq As QueryDef Dim myrs As Recordset

Set mydb = CurrentDB Set myq = mydb.CreateQueryDef("")

myq.connect = "ODBC;" myq.sql = "TEST" Set myrs = myq.OpenRecordset MsgBox myrs!x MsgBox myrs!y

End Function

Keywords: kbhowto kbusage KB128408

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.