ACC2002: Datasheet View Unexpectedly Runs Stored Procedure (282371)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q282371
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

In a Microsoft Access project, when you create a stored procedure and open it in Datasheet view, you expect Datasheet view to display the rows that are to be affected when you actually run the stored procedure. Instead, the stored procedure is unexpectedly run.

For example, if you save a Delete query as a stored procedure, you expect Datasheet view to display the rows that will be deleted when the stored procedure is run. Instead, when you attempt to view the query in Datasheet view, the stored procedure is run and the records are deleted.

RESOLUTION

To review the records that will be affected when the stored procedure is run, build the stored procedure as a select query first. Change the stored procedure to an action query after you have verified that the records that will be affected are the correct records.

If you change an action query to a select query, you have to save the select query before you run it. As a result, some properties of the action query, such as the target table name in a make-table query, are no longer present when you switch back to the action query, and you have to specify them again.

MORE INFORMATION

A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. It is stored in a Microsoft SQL Server database and can be run with one call from a program.

Steps to Reproduce the Behavior

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

  1. Start Microsoft Access and open the sample project NorthwindCS.adp.
  2. In the Database window, click Queries, and then click New.
  3. In the New Query dialog box, select Design Stored Procedure, and then click OK.
  4. In the Add Table dialog box, select Shippers, click Add, and then click Close.
  5. On the Query menu, click Delete Query.
  6. On the File menu, click Save. Name the procedure spDeleteTable, and then click OK.
  7. On the View menu, click Datasheet View to view the records that you want to delete.

    Note that you receive the following message:
    The stored procedure executed successfully but did not return records.
    Click OK to close the message box.
  8. Close the spDeleteTable stored procedure.
  9. Open the Shippers table in Datasheet view.
Note that there are no records in the Shippers table.
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbnofix kbprb KB282371
©2003 Microsoft Corporation. All rights reserved.