MS Access for the Business Environment: Stored Procedures from the MS Access Client

Monday Jun 7th 2004 by William Pearson
Share:

Leverage the efficiencies of Stored Procedures in MSSQL Server from an MS Access client. Join Bill Pearson in creating and executing MSSQL Server Stored Procedures from MS Access to meet the business needs of the enterprise.

About the Series ...

This article continues the series, MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. The series is designed to provide guidance in the practical application of data and database concepts to meet specific needs in the business world. The majority of the procedures I demonstrate in the series will be undertaken within MS Access 2003, although most of the concepts that we explore in the series will apply to earlier versions of MS Access, as well.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: Create a Calculated Field with the Expression Builder. Along with MS Access, of which we have made repeated use in the previous articles of the series, additional application considerations apply for this tutorial, because it introduces another Microsoft RDBMS, MSSQL Server 2000.

For those joining the series at this point because of a desire to work with MSSQL Server 2000 and its components from an MSSQL Server perspective, it is assumed that MSSQL Server 2000 is accessible to / installed on your PC, with the appropriate access rights to the MSSQL Server 2000 environment to parallel the steps of the article. If this is the first time MSSQL Server 2000 is being accessed on your machine, you may need to consult the MSSQL Server 2000 online documentation for installation and configuration instructions.

Introduction to this Tutorial

This article focuses on a topic that in many ways is akin to our last, Extend Access with Pass-Through Queries, in that it is based upon the use of "SQL specific" or "direct SQL" within MS Access. While we have also treated the use of SQL specific queries in other articles, our work with Pass-Through queries in the previous article provides an excellent segue to the use of Stored Procedures within MS Access. The preparation steps of this article will parallel some of the same steps in the last, particularly in the establishment of a data connection. In keeping with my goal of making my articles "standalone," where a reader can perform the steps of the procedures described 1) without having necessarily followed all or part of the series first, and 2) without having to refer to steps in external sources to gain the benefit of an integrated lesson, we will repeat all necessary steps to achieve our objectives from "scratch." My intent is to activate the concepts we introduce within each article through practice examples that are based upon needs that we encounter in the business world, in an end-to-end, complete fashion that allows for "one-stop" exposure to practical subject matter. We will attempt to avoid the broken focus and distraction that come with having to skip about between documents.

Many of us have encountered MS Access in one or more roles in client / server architecture; most of us have been exposed to the use of MS Access as a "front end" to various enterprise-level, back-end databases. As we have mentioned before, MSSQL Server is a natural for the back-end partner in such arrangements, and we can manage communication between MS Access and MSSQL Server in multiple ways. The flexibility of SQL Pass-Through queries, which we discussed at length in Extend Access with Pass-Through Queries, allows us enhanced, efficient communication between MS Access and SQL Server, in that we can use this medium to capitalize upon the use of MSSQL Server-specific SQL to query SQL Server databases with minimal overhead cost at the MS Access level. We will take the pass-through concept one step further in this article and focus on using Access to call Stored Procedures that exist on MSSQL Server, and thereby optimize our interaction with the back-end environment even more.

In this article, we will examine the execution of Stored Procedures from an MS Access client to realize these efficiencies. Our examination of Stored Procedures from an MS Access perspective will include the following:

  • A discussion of the nature of Stored Procedures, and instances in which their use can be beneficial;
  • A discussion of the advantages that accrue when we leverage Stored Procedures from MS Access;
  • Hands-on, practice exercises that include the use of Stored Procedures within MS Access query objects that we create, together with a more involved scenario, in which we create, and then execute, a more substantial Stored Procedure, intended to address a hypothetical business need.


Introduction to Stored Procedures



Like the Pass-Through queries we examined in our last article, Stored Procedures are coded in the dialect of the back end database. Indeed, a Stored Procedure consists, in many cases, of one or more queries just like the one we examined in our last lesson. Stored Procedures are precompiled sets of SQL statements that can perform complex operations on the back-end server that include queries, but can encompass far more.



The Stored Procedure resides on the back-end database server, and exists to perform some action there. The set of SQL statements is given a name, to which we can refer to call the procedure on a recurring basis, to perform its designated action without having to pass the SQL statements repeatedly. This collection and storage of the statements in a modular, reusable object serves as the conceptual foundation of the efficiencies that Stored Procedures offer. A diagram of the execution of the Stored Procedure, on a SQL Server back end from MS Access, is presented in Illustration 1.




Illustration 1: MS Access Executes a Stored Procedure on a MSSQL Server Back End

Stored Procedures are an attractive option in cases that include one or more of the following considerations:

  • Our query will be run repeatedly;
  • Our query involves processing that, while potentially complicated, requires little or no user intervention;
  • We encounter needs that can only be met with complicated logic, or lengthy coding.

While we cannot exploit all the server-based attributes of Stored Procedures from an MS Access client, we can certainly participate in many of the resulting efficiencies within the attributes that we can employ. The advantages of Stored Procedures that are relevant to us from the MS Access perspective include the following:

  • Batching of multiple statements into a single, reusable object;
  • Pre-compilation of an execution plan that can be executed repeatedly;
  • Reduction in network traffic;
  • Control of location of execution;
  • Central maintenance considerations.

Some of these advantages accrue to the use of Pass-Through queries in general, while some make the Stored Procedure even more advantageous than an ad hoc Pass-Through query that accomplishes the same actions. In the simplest sense, Stored Procedures provide a readily accessible storage location for our coding: we often lose external files, forget logic from the past or run into multiple version conflicts when maintaining code in external locations. Moreover, the capability to combine multiple statements in a single, reusable object allows us to batch various "steps" that we do together on a recurring basis, so as to permit us to employ the same steps within the execution of a "single motion." As we shall see, calling the Stored Procedure from MS Access is quite simple, and much easier than typing in a query, or set of queries, that we use repetitively.

In addition to the capability of combining multiple statements into a "one-step call," the Stored Procedure object will run more efficiently on the database server, which means earlier completion of its intended actions and, therefore, a more rapid return of the product of those actions (information) to the MS Access client. Because it is parsed and optimized when it is first executed, and a compiled version of the Stored Procedure remains in the memory cache for ready future use, the Stored Procedure does not need to be reparsed and reoptimized with each use. The result is an often significantly faster execution time.

Additionally, Network traffic is reduced when we call a Stored Procedure, rather than pass the code enclosed within the procedure to the server from the client, because, instead of passing a query through Access JET, then ODBC, to the back-end server, we are passing a much smaller EXECUTE command. This command simply calls the Stored Procedure on the server, and all processing takes place on the back-end thereafter, until results are returned to the client. The benefits of using the Pass-Through query (see the list of advantages in Extend Access with Pass-Through Queries) still accrue in that we avoid the operational "taxes" imposed by the JET / ODBC translation processes, but we gain even more efficiency in that we are not even passing a server-sensitive query, but we are passing a mere command to execute a query, etc., that is fully housed on the back-end server.

The advantage of control of processing location is another benefit we gain from using Stored Procedures that we execute from the client. We can center the processing on the server, which is likely to provide substantially greater processing resources. Moreover, the fact that the Stored Procedure resides on the client provides us with the advantage of central maintenance. If we need to modify the code to meet changing business needs, for example, the Stored Procedure provides a single point of maintenance versus the alternative of making changes to every individual client in a client / server application.

Stored Procedures clearly offer advantages over other options when it is possible to use them instead of ordinary queries. Let's examine the steps involved in setting up Stored Procedures, and expose some of the considerations that accompany their use, in the following sections.

Practice: Establish MS Access as a Front End to MSSQL Server 2000

We will establish the Pubs sample database, which is installed with MSSQL Server 2000 as a part of a typical installation, as the back end for the present exercise once again, primarily to make the article self-contained from the standpoint of working with Stored Procedures from scratch. To do so, we will specify the target database details, and create a connection between MS Access and SQL Server.

NOTE: If you completed this section in the previous article of the series, Extend Access with Pass-Through Queries, you can use the same MS Access database, the same connection, or perhaps both, that we created to the Pubs database for the purposes of that article, to accomplish the procedures that follow this section. If this works best for you, skip this section and begin at the appropriate juncture below.

Let's start MS Access and proceed with the preparation for executing a Stored Procedure on the back-end database from MS Access, specifically with the establishment of a data connection to the targeted Pubs database. To do so, we will take the following steps:

1.  Go to the Start button on the PC, and then navigate to the Microsoft Office Access icon, as we did in the first lesson of this series, Create a Calculated Field with the Expression Builder.

2.  Click the icon to start MS Access.

MS Access opens, and may display the initial dialog. If so, close it.

3.  Select File -> New from the top menu.

4.  Select Blank Database... from the options that are available.

The selection can be made from the Task Pane in MS Access, as shown in Illustration 2, among other ways. Depending upon your version of Access, this may differ.


Illustration 2: Select Blank Database ... (Compact View)

The File New Database dialog appears. Here we give the new database a name and designate where we wish to place it.

5.  Type SQL_Server_Stored_Procedure into the File Name box of the dialog, after navigating to a place to put the database.

The File New Database dialog appears as depicted in Illustration 3.


Illustration 3: The File New Database Dialog

6.  Click the Create button.

The new database is created, and we arrive at the Database window, as shown in Illustration 4.


Illustration 4: The New Database Opens in the Database Window

7.  Click the Queries icon in the Objects pane on the left side of the window.

8.  Click New atop the Query window that appears, as depicted in Illustration 5.


Illustration 5: Click New ...

The New Query dialog appears, from which we can select the means by which we create our query, or, more precisely, the query object that will house the Stored Procedure EXECUTE statement. Because we must use syntax specific to MSSQL Server 2000, as we discussed earlier, we will not be able to use wizardry at this juncture.

9.  Click-select Design View.

The New Query dialog, with our selection, appears as shown in Illustration 6.


Illustration 6: New Query Dialog - Design View Selected

10.  Click OK.

11.  Click Close to dismiss the empty Show Table dialog box that appears.

Just as we cannot rely upon the graphical query design tools to create a Pass-Through query, neither will they assist us in specifying the Stored Procedure we wish to call on the back-end server. We have to manually code here, and our input has to be compatible with the dialectal requirements of the back-end RDBMS. As we did for the Pass-Through query, we will need to leave the graphic query builder and get to the SQL Specific design view.

We are simply using the Pass-Through query option as a conduit through which to send a command to the back-end database. Through this channel, the command will arrive, untranslated, in the syntax that the targeted server understands.

12.  Select Query --> SQL Specific --> Pass-Through on the Query menu, as depicted in Illustration 7.


Illustration 7: Select Query --> SQL Specific --> Pass-Through

The SQL Specific editor appears.

13.  Click the Properties icon atop the view, as partially shown in Illustration 8.


Illustration 8: Click the Properties Icon atop the View (Partial View)

The Query Properties page opens. Here we will assign connection information for the back-end server. As most of us are aware, we might have created ODBC connections in advance, or perhaps have a connection already set up that we might use for our immediate purposes, but we will walk through the complete process here, as if we were doing it "on the fly," so as to cover all the bases. For more information on establishing an ODBC connection, see the MSSQL Server documentation, MS Access documentation, or other relevant sources.

NOTE: If we do not specify a connection string here, MS Access will use the default that initially appears, "ODBC." If we take that route, we will be prompted for connection information at query run time.

Please note also that some of the connection information that I depict in the illustrations will obviously need to be supplanted with settings that are contextually correct from the perspective of your own environment.

14.  Click Build, the ellipses icon ("...") that appears on the right side of the ODBC Connect Str box on the Query Properties page, as shown in Illustration 9.


Illustration 9: Click the Ellipses ("...") Icon to the Right of the ODBC Connect Str Box

The Select Data Source dialog appears, defaulted to the File Data Source tab. While there are various options for the types of source we can establish, we will create a Machine Data Source at this point.

15.  Click the Machine Data Source tab.

16.  Click New.

The Create New Data Source dialog appears.

17.  Select the System Data Source radio button.

The Create New Data Source dialog appears as depicted in Illustration 10.


Illustration 10: The Create New Data Source Dialog

18.  Click Next.

19.  Scrolling as necessary in the next Create New Data Source dialog that appears ("Select a driver ..."), select the SQL Server driver, as shown in Illustration 11.


Illustration 11: Select the SQL Server Driver

20.  Click Next.

The third Create New Data Source dialog appears, confirming our selections, as depicted in Illustration 12.


Illustration 12: The Confirmation Dialog

21.  Click Finish.

The Create a New Data Source to SQL Server dialog appears.

22.  Type the following in the Name text box of the dialog:

Pubs DB

23.  Type the following in the Description text box of the dialog:

Pubs DB Back End

24.  In the Server selector box, select the server to which you wish to connect (mine is MOTHER in the illustrations).

The Create a New Data Source to SQL Server dialog appears as shown in Illustration 13.


Illustration 13: The Create a New Data Source to SQL Server Dialog

25.  Click Next.

26.  Enter the appropriate authentication setting for security by clicking the respective radio button in the next dialog. (Mine is Windows NT authentication, as depicted in Illustration 14.) Leave the other settings at default.

Click for larger image

Illustration 14: Select the Appropriate Authentication Setting

27.  Click Next.

28.  On the next dialog to appear, click the radio button to the left of the top entry, Change the default database to: , to activate it.

29.  Select pubs in the selector box immediately under Change the default database to: , and leave all other settings at default.

The third Create a New Data Source to SQL Server dialog appears as shown in Illustration 15.


Illustration 15: Dialog with Settings

30.  Click Next.

The next dialog of the series appears. We will leave all setpoints therein at default, as shown in Illustration 16.


Illustration 16: Fourth Dialog, Create a New Data Source to SQL Server Series

31.  Click Finish.

The ODBC Microsoft SQL Server Setup dialog appears, confirming our configuration, as depicted in Illustration 17.


Illustration 17: ODBC Microsoft SQL Server Setup Dialog

Let's test the connection at this point to ascertain correct setup.

32.  Click the Test Data Source button on the ODBC Microsoft SQL Server Setup dialog.

The SQL Server ODBC Data Source Test message box appears, in short order, indicating we have tested positive for connectivity, as shown in Illustration 18.


Illustration 18: SQL Server ODBC Data Source Test Message Box - Connectivity Confirmed

33.  Click OK to close the message box.

34.  Click OK to close the ODBC Microsoft SQL Server Setup dialog that reappears.

We are returned to the Select Data Source dialog, Machine Data Source tab, where we can see our new data source, Pubs DB, appear among the selections, as shown in Illustration 19.


Illustration 19: Select Data Source Dialog - Machine Data Source Tab, with Our New Source

35.  Ensure that the new data source Pubs DB is selected by clicking / highlighting it.

36.  Click OK.

The Connection String Builder - Save Password dialog appears, as shown in Illustration 20.


Illustration 20: Connection String Builder - Save Password Dialog

Here we can select Yes if we wish to save the logon / password information within the connection string we are building (as appropriate).

NOTE: Saving logon information in connection strings carries inherent risk. Security of the data source may be compromised by doing so. For more information, consult the MS Access documentation, MSSQL Server 2000 Books Online, or other resources. Not saving the information will result in our being asked for logon / password information each time we attempt to access the back-end via MS Access.

Since mine is a development environment, I will save the information, but you may do as you see fit for your own environment.

37.  Answer the dialog by selecting Yes or No, as appropriate to your environment.

We are returned to the Query Properties page, where we see the new string specification appear in the ODBC Connect Str box, as shown in Illustration 21.


Illustration 21: Query Properties Page - New Connection Information in Place

Another property that is of significance within our intended use of the Pass-Through query channel is the Returns Records setting. We will leave this setting at its default of Yes (as indicated in Illustration 21) for the Stored Procedure we will execute in our first example. This is because the procedure we will use is designed to return records, in a report-like manner, for viewing at the client level. In cases where the procedure is intended to perform an action other than the simple return of a recordset, we change the Return Records setting to No. If we leave it at the default of Yes in a case where records are not returned, we receive an error message.

38.  Close the Query Properties page.

We are now ready to work with a Stored Procedure.

Practice: Execute a Stored Procedure from the MS Access Client

Our next steps will involve the simple execution of a Stored Procedure to get a feel for this straightforward process. To do this in the easiest manner, we will use a procedure that we know to exist in MSSQL Server 2000 already. Whether we have ever created a Stored Procedure in our copy of the Pubs sample database or not, we can still experience the execution of a Stored Procedure by using a System Stored Procedure.

The master database on MSSQL Server contains a sizable collection of administrative Stored Procedures that come along as part of the installation. These procedures, prefixed with sp_, can be very useful in managing and monitoring the database in general. I have found some innovative uses for these procedures, particularly in metadata reporting and other contexts, while working with clients over the years. My purpose in bringing System Stored Procedures into our current focus is simply to explain that we are using a Stored Procedure that already exists on the server, as it is automatically placed there from the start. This way, we can initially focus on the execution of the Stored Procedure, without the distraction of creating a procedure to execute first.

Let's say that we have been asked by the leader of a project team within the organization to provide a simple means of monitoring the databases on the back-end server. The users of this functionality will be MS Access developers who do not have MSSQL client utilities on their machines. (The back-end development server, to which the developers have ODBC connectivity - with access privileges - similar to that we have established in the forgoing section, is in another state.)

We are told that the developers need to be able to easily obtain size information about all the databases on the server, to be able to monitor their own consumption of resources as they perform data imports, table updates and other activities, from the evolving front-end environment.

Because the requirements are minimal, and because we want a "quick and dirty" solution, we will use a System Stored Procedure, sp_helpdb, which provides a list of the databases on the server, along with size information and a few other facts about each, in a minimally formatted recordset.

We will meet the needs of the information consumers by taking the following steps:

1.  Type the following command into the SQL Pass-Through Query window:

EXECUTE sp_helpdb

(We might have used EXEC, instead of EXECUTE, to obtain the same results. For more information on the System Stored Procedures, as well as for Stored Procedures in general, see the MSSQL Server 2000 Books Online, or other MSSQL Server SQL references. Our purpose here is to investigate the execution of a back-end Stored Procedure from an MS Access client, rather than to perform an in-depth examination of Stored Procedures themselves.)

The SQL Pass-Through Query window appears, with command in place, as shown in Illustration 22.


Illustration 22: SQL Pass-Through Query Window, with our EXECUTE Command in Place

The Stored Procedure through which we seek to interact directly with the back-end server is now in place. Like a Pass-Through query, the command will avoid the "taxation" of simply executing a query to obtain the same information, by bypassing two translation steps within the JET engine and ODBC, merely flowing through them to the back-end RDBMS instead.

2.  Execute the command by clicking the Run button (see Illustration 23) on the toolbar.


Illustration 23: Execute the Command using the Run Button

As was the case with the Pass-Through query in our earlier article, had we not saved the connection information, or had we forgone the insertion of the connection string in the ODBC Connect Str box of the Query Properties page, we would be prompted for connection / logon information at this point.

The command executes sp_helpdb, and the results dataset is returned, as partially shown in Illustration 24.


Illustration 24: The Results Dataset Appears (Partial View)

NOTE: Results will obviously reflect the databases that reside on your own server.

We thus see the results of executing a SQL Server System Stored Procedure against a back-end server using the Pass-Through query conduit in MS Access.

3.  Select File --> Save As and name the query Server DB Information, as shown in Illustration 25.


Illustration 25: Saving the Stored Procedure Command for Re-use

4.  Click OK.

5.  Close the results dataset view.

We see the new Stored Procedure displayed, alongside the characteristic Pass-Through icon, as shown in Illustration 26.


Illustration 26: The New Stored Procedure Command Appears in the Query View

Having saved the Stored Procedure as a Pass-Through query object, we have left it in place for reuse at any time that the development team needs to examine space statistics, meeting the need expressed by the project manager. Next, we will examine how we might handle a specific reporting need with data from the Pubs database - specifically, with a Stored Procedure we create for that purpose - all from inside MS Access.

Practice: Create a New Stored Procedure to Meet a Business Need

As an illustrative example, let's say that we are next asked by the accounting department of our publishing organization for information to meet a recurring business need. Accounting needs a crosstab report that summarizes, from the Pubs database, quarterly sales volumes by sales year.

The information consumers from whom we gather the business requirements state that the report will become a recurring requirement, and will be run frequently to update quarterly volumes that, in our business, are subject to adjustments due to returns and the like. Our developers have the same restrictions that we mentioned in our first example with regard to the MS Access connection being their only connection to the database.

The query required to fill the requirement will be a likely candidate for a Stored Procedure, at least as one means of helping the information consumers to meet their stated objectives. Once we create such a Stored Procedure, we can generate the report prospectively at any time, with the click of a mouse.

Let's meet the needs of the information consumers by taking the following steps:

1.  From the Query window, where we left MS Access in our last exercise, click New atop the window:

The New Query dialog appears.

2.  Click-select Design View, once again.

3.  Click OK.

4.  Click Close to dismiss the empty Show Table dialog box that appears.

We will use the Pass-Through query option as a conduit, once again, through which to send a CREATE PROCEDURE statement that includes the query we need to generate the report for the information consumers. Through this channel, we will again communicate syntax that the targeted database server understands.

5.  Select Query --> SQL Specific --> Pass-Through on the Query menu, as we did in the earlier section.

The SQL Specific editor appears.

6.  Click the Properties icon atop the view, again as we did earlier.

The Query Properties page opens. This time, 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.

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, as shown in Illustration 27.


Illustration 27: Select the Pubs_DB Data Source

10.  Click OK.

11.  On the Connection String Builder message box that next appears, click the answer you gave earlier, in our initial response to its question regarding the storage of the password.

As before, I clicked Yes here, as I am in a development environment with minimal risk involved in storing a password in the connection string. Please see the warning in the first section of this article regarding doing so in your own environment.

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.

Another property we need to consider, again because it is of significance within our intended use of the Pass-Through query channel, is the Returns Records setting. This time, we will set it to No, as shown in Illustration 28.


Illustration 28: The Properties Page, with Our New Settings

We will be writing a one-time CREATE PROCEDURE statement, containing the query necessary to generate the report we have been tasked to create, to place the custom Stored Procedure on the back-end SQL Server. Then, anytime the data needs to be generated, we can execute the Stored Procedure to obtain updated information for our information consumers. No recordset will be returned from the CREATE PROCEDURE statement, therefore Return Records is appropriately set to No.

12.  Close the Query Properties page.

We are returned to the SQL Pass-Through Query window.

We are now ready to create a back-end Stored Procedure.

Practice: Create a Stored Procedure from the MS Access Client

We will first create the Stored Procedure, then run it with another EXECUTE command, as we did earlier with the System Stored Procedure.

1.  Type the following syntax into the SQL Pass-Through Query window:


CREATE PROCEDURE crosstab_salesvol_qtrly
AS

SELECT  DATEPART(year, ord_date) 
	'Sales Year',

  SUM(CASE DATEPART (quarter, ord_date) 
    WHEN 1 THEN qty ELSE 0 END) [Q1 Vol],

  SUM(CASE DATEPART (quarter, ord_date) 
    WHEN 2 THEN qty ELSE 0 END) [Q2 Vol],

  SUM(CASE DATEPART (quarter, ord_date) 
    WHEN 3 THEN qty ELSE 0 END) [Q3 Vol],

  SUM(CASE DATEPART (quarter, ord_date) 
    WHEN 4 THEN qty ELSE 0 END) [Q4 Vol]

   FROM sales

   GROUP BY DATEPART(year, ord_date)

We will call our Stored Procedure crosstab_salesvol_qtrly to make it easier to locate, should someone need to edit it and so forth.

The SQL Pass-Through Query window appears, with CREATE PROCEDURE statement in place, as shown in Illustration 29.


Illustration 29: SQL Pass-Through Query Window, with our CREATE PROCEDURE Statement

The CREATE command for the Stored Procedure we seek to create directly on the back-end server is now in place.

2.  Execute the statement by clicking the Run button on the toolbar.

The command executes the CREATE PROCEDURE statement, and Stored Procedure crosstab_salesvol_qtrly is created. Although no feedback is received (no convenient message box, for example, announcing "Stored Procedure created!" or words to that effect), we can see the Stored Procedure if we go to SQL Server Enterprise Manager and expand the Pubs database, then expand the Stored Procedures object, as shown in Illustration 30.


Illustration 30: The Stored Procedure Appears on the Back-End Database Server

NOTE: We will not go through the navigation of getting to the above. If you need help, see the MSSQL Server 2000 Books Online. I will mention, however, that a great way to review the code within a Stored Procedure at the server level is simply to right-mouse the associated object, and select Properties: the internals appear as shown in Illustration 31.


Illustration 31: The Code behind the Stored Procedure ... Enterprise Manager Properties Page

It is important to note, too, that permissions can be managed at the procedure level - meaning we can control who can access and execute the procedure in a relatively granular manner - another advantage of using Stored Procedures from the MS Access client.

Let's save our work - we can adjust it here, too, if we need, but we would have to drop the old procedure and replace it with another freshly created one. Another subject for another day ...

3.  Select File --> Save As and name the query Create_SP_crosstab_salesvol_qtrly.

4.  Click OK.

5.  Close the SQL Pass-Through Query window.

We see the new Stored Procedure displayed, as shown in Illustration 32.


Illustration 32: The Pass-Through Creation Query Appears in the Query View

And so, we see that our Stored Procedure is safely nestled on the server, awaiting our command to deliver the data we designed it to present. In reality, of course, we would have ascertained this was the case by running the query first, but the focus here is creating the Stored Procedure from MS Access, and we shall see the results in our next section.

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved