ACC: How to Return Values from SQL Stored Procedures

 Last reviewed: May 20, 1997
Article 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
Hardware : X86
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.