Adapting BOL Examples for Access

Learn by example by using stored procedures from Books Online with Access projects

SQL Server Books Online (BOL) is one of the best routes for getting up to speed on SQL Server, which might be why Microsoft makes BOL available with SQL Server and the Microsoft Office 2000 Developer Edition (MOD). (BOL is called Microsoft SQL Server Programmer's Toolkit on the MOD CD-ROM.) Many BOL code examples target the SQL Server Query Analyzer. Although the examples run fine in that environment, you often need to adjust the example to work in the stored procedure templates available with Access projects or in Visual Basic for Applications (VBA) projects.

You can practice using these basic procedures by repeatedly running a stored procedure from a VBA procedure, setting parameter values, and using selected default values. Mastering these general usage principles can help Access developers get the most out of BOL.

Using the First Create Procedure Example

Searching for CREATE PROCEDURE (T-SQL) in BOL opens a documentation page rich in explanation and examples of how to use T-SQL in stored procedures. The first example creates a stored procedure that lists author names, book titles, and publishers from the Pubs database. The example shows how to conditionally drop a prior version of the stored procedure, if it already exists, before including the code to create the new version. You need to know where to copy this code into the stored procedure template for an Access project. But even if the initial copy is correct, some keywords in the example don't work from Access projects.

To adapt this example code, create a new Access project that connects to the Pubs database. With the new .adp file format, you can select Stored Procedures from the Objects bar, then click the New icon on the Database container. This selection opens a stored procedure template you can copy the code into. Insert the example code so that it replaces template content after the As keyword.

Listing 1 shows the example after it's been edited as necessary to run it in an Access project. The code demonstrates some general editing guidelines. First, comment out lines with the USE keyword; Access projects don't recognize it. Comment out lines with the GO keyword for the same reason. Then, copy the CREATE PROCEDURE line from the example in place of the Create Procedure line at the top of the template to assign the correct name to the stored procedure. Note that after you save and reopen the stored procedure, Access automatically converts CREATE PROCEDURE to ALTER PROCEDURE. Finally, you can comment out the lines that conditionally drop the previous version of the stored procedure. The Access project conversion from CREATE PROCEDURE to ALTER PROCEDURE eliminates the need to drop a previous version.

You can simplify the syntax and speed the operation of this example by initially using ALTER PROCEDURE instead of CREATE PROCEDURE. Permission to use the ALTER PROCEDURE statement resides with members of the db_owner and ddl_admin roles, so if you log on to SQL Server as sa or the database owner, you meet this requirement.

Using the Second and Third Create Procedure Examples

The second and third examples on the CREATE PROCEDURE (T-SQL) documentation page illustrate how to design parameters into a stored procedure. This capability enables users to vary the behavior of a stored procedure at runtime. Both examples extend the first example to list author name, title, and publisher. The second example declares parameters but doesn't assign any default values. The third example demonstrates the syntax for referencing default values.

Listing 2 shows the edited code for the third example. This code applies the editing guidelines from the first example and adds one more. In particular, it copies the parameter names and assignments from just after the CREATE PROCEDURE statement to the correct location between the CREATE PROCEDURE statement and the As keyword in the new stored procedure. Then it comments out those lines from the body of the example. One crucial design difference between the third example (from Listing 2) and the second example (see BOL) is that the second BOL example has no equals (=) sign in the parameter declaration assigning a default value. Another important distinction is the use of the LIKE operator in Listing 2 for specifying the au_fname and au_lname criteria in the WHERE clause at the end of the stored procedure. Using a LIKE operator along with the default parameter assignments in the third example lets you specify just part of an author's first or last name. The parameter declaration area also illustrates the difference in wildcard designation. Access developers moving from Jet to SQL Server or MSDE must remember to use % and _ instead of * and ? as wildcard symbols.

The design differences between the third and second examples have a radical impact on their behavior when you run them directly from the Access project's Database window. In a demonstration file, I saved the second example with the name au_info and the third one with the name au_info2. Double-clicking au_info in the Database window immediately opens a dialog box that prompts you for an author's last name. Screen 1 shows this box with an entry of Dull for the last name. After you click OK, a dialog box for the first name appears. If a match exists for the first and last name entries, the query in the stored procedure returns the corresponding record. Double-clicking au_info2 in the Database window always yields the return set that Screen 2 shows: three records matching a lastname parameter of D% and a firstname parameter of % (the default values for au_info2). In general, running a stored procedure with default values for its parameters from the Database window doesn't let you assign parameter values.

How do you set the parameters at runtime for a stored procedure with default parameter assignments? The documentation for the third example includes a script that successively runs au_info2 with different settings for its parameters. However, the script doesn't work as described when you copy it into an Access project stored procedure template and run it from the Database window.

Listing 3 shows the script in a stored procedure named Runau_info2. When you run the script from the BOL documentation in Query Analyzer, it generates six return sets, one for each EXECUTE statement in the script. If you run the script without commenting out any EXECUTE statements, the stored procedure always returns the three records that appear in Screen 2. This return set matches the first EXECUTE statement in Listing 3. If you comment out the first EXECUTE statement, running the stored procedure generates a return set that matches the second EXECUTE statement. In general, invoking Runau_ info2 generates just one return set, the one for the first EXECUTE statement that isn't commented out. Therefore, you can successively examine the return sets for all the EXECUTE statements by running the procedure six times, commenting out a different five statements on each run.

Running a Stored Procedure with VBA

Experienced VBA and ADO developers will find it straightforward to use ADO to run a SQL Server stored procedure repeatedly in Access and collect the return sets from each run. Because you're using ADO, you can also download multiple return sets from a stored procedure to use with other Office components, such as Word 2000 and Excel 2000.

The listing "VBA Procedure for Repeatedly Running the au_info2 Stored Procedure," which you can download from the file 8237.zip at the link to this article (ID 8237) on the SQL Server Magazine Web site at http://www.sqlmag.com, includes a Sub procedure to run the third example from the CREATE PROCEDURE (T-SQL) page of the BOL documentation. The listing demonstrates how to use VBA and ADO to capture multiple return sets as if you were running all the EXECUTE statements in Listing 3 from within Query Analyzer. Because the VBA procedure uses the ADO Command and Parameter objects, your VBA project needs a reference to the Microsoft ADO library. Access 2000 automatically provides this reference, but other Office 2000 components require you to add it. You can add the reference by first choosing Tools, References from the VBA project menu. Next, select the check box for Microsoft ActiveX Data Objects 2.1 Library in the References dialog box. Finish by clicking OK.

After initially declaring a Command object and a pair of Parameter objects with Dim statements, the procedure in the online listing instantiates cmd1 as a Command object instance. Next, the procedure defines properties for cmd1, instantiates a pair of parameters, and appends them to the Parameters collection for cmd1. This series of actions occurs within a With...End statement. The CommandText property for cmd1 names the SQL Server stored procedure to run when you execute the command, and the CommandType property informs the ADO processor that the CommandText property designates a stored procedure's name, not a SQL string. After these preliminaries, the VBA procedure executes cmd1 six times. The six successive executions are similar to the six EXECUTE statements from Listing 3. (They aren't identical because they go through the ADO interpreter before going to SQL Server, whereas the EXECUTE statements in Listing 3 go through the T-SQL interpreter before going to SQL Server. The VBA/ADO example rewrites a BOL example to simulate its effect with comparable ADO statements.) A call to the PrintReturns Sub procedure in Listing 4 extracts the data that each run of cmd1 generates and prints them in the Immediate window. The Sub procedure in Listing 4 accepts the return set in the cmd1 instance and assigns it to a record set instance. Then, the Sub procedure iterates through the columns of each successive row in the record set. At the end of each row, it prints a string revealing the items on that row.

The first execution of cmd1 passes back a return set for the default parameter values of au_info2. The code for this cmd1 execution doesn't assign values to prm1 or prm2; both parameters have an Empty value, so the au_info2 stored procedure uses its default settings for the first and second parameters. Before executing cmd1 a second time, the VBA procedure in the online listing assigns Wh% to prm1. The second execution of cmd1 doesn't assign a value to prm2, so the stored procedure uses its default value of % for first name. Before executing cmd1 a third time, the procedure in the online listing assigns A% to prm2 and clears the Wh% assignment for prm1 by explicitly setting it to Empty. This setting causes the command to return a record for Ann Dull (because D% is the default value for the @lastname parameter that prm1 references). You need to set a parameter to Empty after assigning it a non-empty value if you want to use a parameter's default value. The fourth, fifth, and sixth executions of cmd1 demonstrate different formats for specifying parameters to a SQL Server stored procedure.

BOL is an important resource for easing the transition from Jet databases to MSDE or SQL Server. Being familiar with some potential problems and workarounds for these problems can help Access developers as they use BOL, stored procedures, and T-SQL.