KB128408 - ACC: How to Return Values from SQL Stored Procedures

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

ACC: How to Return Values from SQL Stored Procedures

SUMMARY

MORE INFORMATION