ACC: How to Return Values from SQL Stored Procedures

ID: Q128408

The information in this article applies to:

Microsoft Access versions 2.0, 7.0

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 : kbusage OdbcSqlms
Version : 2.0 7.0
Platform : WINDOWS
Issue type : kbhowto