MS Access for the Business Environment: Stored Procedures from the MS Access Client - Page 9
June 7, 2004
Practice: Execute the Newly Created Stored Procedure
Let's follow through and deliver the information to the waiting information consumers with our new Stored Procedure. We will do so by taking the following steps:
1. Click New atop the Query window, as we have before.
2. Click-select Design View on the New Query dialog that appears.
3. Click OK.
4. Click Close to dismiss the empty Show Table dialog box that appears.
We will use the Pass-Through Query option again as a conduit through which to send an EXECUTE command to the back-end database.
5. Select Query --> SQL Specific --> Pass-Through on the Query menu.
The SQL Specific editor appears.
6. Click the Properties icon atop the view.
The Query Properties page opens. We will assign connection information for the back-end server using the data source we created in the first part of our exercises, Pubs_DB, once again.
7. Click Build, the ellipses icon ("...") that appears on the right side of the ODBC Connect Str box on the Query Properties page, as we did earlier.
The Select Data Source dialog appears, defaulted to the File Data Source tab.
8. Click the Machine Data Source tab.
9. Click-select the Pubs_DB data source we created in the first section.
10. Click OK.
11. On the Connection String Builder message box that next appears, click the answer you gave earlier in our initial exposure to its question regarding the storage of the password.
I clicked Yes here, as I have discussed before.
We are returned to the Query Properties page, where we see the new string specification appear in the ODBC Connect Str box, as we noted in the first section of the article.
We are establishing a means to execute, on recurring basis, the Stored Procedure we have created, crosstab_salesvol_qtrly, containing the query necessary to generate the information needed by our information consumers. We therefore expect a recordset to be returned with each execution.
12. Ensure that Return Records is appropriately set to Yes.
13. Close the Query Properties page.
We are returned to the SQL Pass-Through Query window, where we are ready to build the EXECUTE statement for our Stored Procedure.
14. Type the following command into the SQL Pass-Through Query window.
The SQL Pass-Through Query window appears, with command in place, as shown in Illustration 33.
The capability to easily execute our Stored Procedure is now in place, and ready to call the procedure to provide the metrics that the information consumers need at any time.
15. Execute the command by clicking the Run button on the toolbar.
The command executes the Stored Procedure crosstab_salesvol_qtrly, and the results dataset is returned, as shown in Illustration 34.
We thus meet the business requirements of our information consumers, providing a quick and easy way to request the same data on a recurring basis, to allow us to accurately reflect the results of our business operation at any given point in time.
16. Select File --> Save As and name the query Quarterly Volume Crosstab, as shown in Illustration 35.
17. Click OK.
18. Close the results dataset view.
We see the new Stored Procedure displayed, as shown in Illustration 36.
We can now produce the crosstab report at any time that it is requested, with point-and-click ease and speed.
In this article, we explored the nature of Stored Procedures in the back-end server environment, and then set out to examine the use of Stored Procedures from an MS Access client, to realize their innate efficiencies. We discussed advantages that accrue when we leverage Stored Procedures from MS Access, and showed how their use, when appropriate, can actually be more efficient than the Pass-Through queries we discussed in our earlier article. We then undertook hands-on practice exercises that included the use of an MSSQL Server System Stored Procedure within an MS Access query object that we created, together with a more involved scenario, in which we created, and then executed, a more substantial Stored Procedure intended to address a hypothetical business need. Finally, throughout our practice examples, we commented on the fitness of remotely actuated Stored Procedures for the purpose of meeting a recurring business need from an Access client connected to an MSSQL Server back-end database.