ACC: How to Return Values from SQL Stored Procedures

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.

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:

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: