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.
EXECUTE crosstab_salesvol_qtrly
The SQL
Pass-Through Query window appears, with command in place, as shown in Illustration
33.
Illustration 33: SQL Pass-Through Query Window, with our
EXECUTE Command in Place
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.
Illustration 34: The Results Dataset Appears
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.
Illustration 35: Saving the Stored Procedure Command for
Re-use
17.
Click OK.
18.
Close the
results dataset view.
We see
the new Stored Procedure displayed, as shown in Illustration 36.
Illustration 36: The New Stored Procedure Command Appears
in the Query View
We can now produce the crosstab report at any time that it
is requested, with point-and-click ease and speed.
Conclusion ...
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.
»
See All Articles by Columnist William E. Pearson, III
» 
Comment and Contribute
