MS Access for the Business Environment: Stored Procedures from the MS Access Client - Page 6
June 7, 2004
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:
(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.
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.
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.
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.
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.
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.