SQL/Office Integration: Using Stored Procedures with Access Projects

Stored procedures provide expanded and dynamic functionality

SQL Server developers know stored procedures as powerful tools, but Access developers switching to SQL Server with Access projects might find the syntax of and techniques for building and using stored procedures unfamiliar. Stored procedures are a new, important topic for Access developers. Before Access 2000, Access developers couldn't create stored procedures. With Access 2000 and Access projects, you can write your own Transact SQL (T-SQL) scripts in stored procedures. And now SQL Server developers can write stored procedures in Access.

Stored procedures fall into two broad categories: system stored procedures and user-created stored procedures. You can use system stored procedures to report meta data about a SQL Server database and to move database files from one server to another. One use of this process is moving a database file from a development server to a production server. You can use user-created stored procedures to manage parameters and return states. These parameters and return states let you pass arguments into a stored procedure and extract data from it.

Reporting Meta Data with System Stored Procedures

System stored procedures group into several categories, such as catalog procedures, cursor procedures, distributed queries procedures, SQL Server Agent procedures, and replication procedures. You can learn more about all the categories, including the names of individual system stored procedures and their syntax, from SQL Server Books Online (BOL). Catalog system stored procedures provide meta data about a database—that is, they tell you about the structure, not the contents of a database. The sp_tables procedure returns information about system tables, user-created tables, and views. You can let the procedure return meta data for all the tables and views in a database, or you can specify sp_tables arguments to return data about a subset of the tables and views. This system stored procedure returns a five-column recordset containing information such as the qualifier (database name), owner, name, and database object type. Screen 1 shows an excerpt from a return set.

Invoking sp_tables is straightforward. Open an Access stored procedure template in the Design view by selecting Stored Procedures in the Objects bar of the Access project Database window and clicking New. Next, replace all text after the As keyword in the template with Exec sp_tables. Then click the Save button on the toolbar and close the stored procedure template. You can launch this procedure from the Database window by highlighting the stored procedure name and clicking Run. Rows that have a TABLE_TYPE equal to TABLE reference user-defined tables. System tables have the type SYSTEM TABLE. However, no comparable TABLE_TYPE value distinguishes between user-created views and system-created views—all have the value VIEW.

Listing 1, page 18, illustrates a Visual Basic for Applications (VBA) approach to running stored procedures. To enter this code, open the VBA editor by pressing Alt+F11 from the Database window. Then, click the Insert Module button on the toolbar. Finally, enter the code into the code window. The VBA procedure EnumerateViews opens an ADO recordset based on the return set from msp_EnumerateTables (a user-created stored procedure). A Do Until loop selects only rows with a TABLE_TYPE value of VIEW. Additional formatting extracts two columns from the return set and positions them within a VBA project's Immediate window.

The sp_columns system stored procedure provides meta data about the columns in a set of tables or views. Through arguments designating a table's name, owner, or qualifier, you can specify which set sp_columns reports on. Also, you can constrain the return set by designating a column name or ODBC version. The return set from sp_columns contains a row for each column in the set. This return set has 19 columns of data for each row, some of which will interest only ODBC programmers because some columns designate information that non-ODBC programmers can't use or specify. You can recognize the rows in a return set by column name, table or view name, owner, and qualifier. And you can readily discover a column's data type, precision, scale, length, and whether it is nullable.

The syntax for using sp_columns in an Access project is similar to that for sp_tables. Just open a new stored procedure template and replace all text after the As keyword with Exec sp_columns plus some information denoting which columns you want information about. You can use any of several optional arguments, such as table name, table owner, table qualifier, column name, and ODBC version, to specify the information. For an Access project connected to the Pubs database, you can get a report about the columns of the authors table by replacing the text after the As keyword in the stored procedure template with Exec sp_columns authors. Then, save the user-created stored procedure and run it as described above.

Detaching and Attaching Database Files

As you deploy solutions with Access, you might need to detach a database from one server and attach it to another. Access developers who transition from Jet to SQL Server or Microsoft Data Engine (MSDE—now called SQL Runtime) need to understand that a database is more than just a file. Both SQL Server and SQL Runtime actively manage their database files, so you can't copy a database while it's attached to a server, and you must attach the database to a server before you can use it. (For more information about moving to MSDE, see "What Is MSDE?" February 2000.) SQL Server exposes database objects based on these files. You can't just copy an actively managed file from one server to another; you need to detach the file before copying it to another server. After copying the database files, you have two options for attaching them to the new server. You can attach just the database file (with an .mdf extension) or both the database file and its log file (with an .ldf extension). Attaching just the database file is convenient for emailing a database from one location to another. When a database administrator or developer attaches just the database file, SQL Server automatically creates a new log file. Copying both files preserves the log from the original database server on the new server. In general, transferring both files between servers is safer.

You use the sp_detach_db system stored procedure to detach a database from a server. The database files remain in the Data folder of the Microsoft SQL Server directory (Mssql7), but they become available for copying to another server. After copying the files to a different server, you can reattach the database files to the original server. Doing so lets you maintain copies of the database on two or more servers.

Running sp_detach_db is simple: Just precede it with the Exec keyword and follow it with the name of the database that you want to detach. The example script in Listing 2 detaches the Pubs database. The stored procedure demonstrates how to introduce conditional branching and how to use the RAISERROR statement to send a message. RAISERROR is more flexible than the Print statement. If the database isn't currently attached, msp_ DetachLocalPubs informs the user of that fact with a pop-up Access dialog box. Otherwise, the script invokes the sp_detach_db system stored procedure.

After detaching a database, you can use the Windows Explorer or copy command to copy the database's files from the Data folder for one server to the corresponding folder for another server. No matter how you copy a database's files, you'll need to attach the copy to the new server (and possibly the original files to the original server). Two stored procedures exist for attaching database files to a server. Invoke sp_attach_db when you want to attach both the database file and the log file. If you aren't attaching the log file, use sp_attach_single_file_db.

Listing 3 displays three stored procedures for use within an Access project that illustrate how to detach and attach databases. The first procedure, msp_AttachLocalPubs, demonstrates one way to run the sp_attach_db system stored procedure. Notice that it requires three arguments: one to name the database, one for the .mdf file, and one to reference the log file. The second procedure, msp_UnconditionallyAttachLocalPubs, invokes two stored procedures. This example first executes msp_ DetachLocalPubs (from Listing 2) to remove a database, then invokes msp_AttachLocal Pubs to attach the database and log files. By altering arguments within the stored procedures, you can detach a database and change the database name when you copy it to the new server.

The last stored procedure in Listing 3 shows how to use an input parameter with a stored procedure. In this case, the input parameter specifies the name of a database to attach by means of the sp_attach_single_file_db system stored procedure. The default name for the database is Pubs, but by replying to a prompt, you can specify any other name for the database.

Sorting Databases and Counting Rows

Stored procedures bring unique functionality to SQL Server solutions that views don't offer. For example, views don't enable sorted return sets. You can sort an exposed return set in a view, but you can't pre-specify an order for its return set; views don't enable the ORDER BY phrase or parameter queries. Stored procedures, however, enable both.

Listing 4 presents two procedures for generating sorted return sets. The msp_SortedContacts stored procedure sorts the records of the tblContacts table in the current database. This code is directly analogous to the comparable Jet SQL statements. The second stored procedure, msp_SortedAuthors, shows the syntax for returning a sorted return set from a table in another database. The T-SQL statements in this procedure qualify the table's name with its owner and a database name. Also, no argument designates direction for the sort because the default, ascending order, is acceptable. However, showing the Asc keyword, as in the msp_SortedContacts stored procedure, has the advantage of marking where to place Desc if you later want the return set in descending order.

Listing 5 demonstrates an automatic way to make the sort order conditional. The msp_CallSortUpOrDown procedure calls another procedure and passes an argument to it designating the sort order. The second stored procedure, msp_SortUpOrDown, takes the input parameter and conditionally executes one of two T-SQL statement blocks. BEGIN and END keywords delimit each block. An IF...Else statement transfers control to one of the blocks based on the passed parameter. If an application launches msp_SortUpOrDown without specifying a sort order, the stored procedure uses descending order.

Stored procedures offer rich development options to Access developers switching from Jet databases to SQL Server and SQL Runtime database servers. By using system stored procedures, developers can take advantage of built-in programs that come with SQL Server. With user-created stored procedures, developers can provide functionality that views don't offer, such as sorting the results from a SELECT query and passing parameters to make SELECT queries dynamic when you run them. In future columns, I'll address in more detail how to use stored procedures to build solutions with Access 2000.