Stored Procedure tricks

If you’re using SQL Server as your back-end database, you’ll get your best performance by using stored procedures. Here, Michael McManus showcases some standard routines to make using those tools a little easier.

As Mary Chipman and Mike Gunderloy emphasized in their August 1999 Smart Access article (“Client/Server Development with Access”), one of the secrets to successful client-server development with Access is using stored procedures to replace most, if not all, of your native Jet queries. Unfortunately, moving your queries to the server creates a new problem: Once you’ve replaced your Jet queries with stored procedures on the server, how do you call these routines when they require parameters? To simplify the process, I’ve written some standard routines that make using stored procedures a little easier.

Calling action queries

The following function, when passed a valid SQL string, will execute a stored procedure. The function can be reused throughout your application:

You can use this routine to execute Update, Insert, or Delete commands. If all goes well, the routine returns True, while any error causes the function to hand back a False. The variable gstrConnect contains the connect string for the database (see the sidebar “DSN-Less Connections”).

Here’s an example that executes the Stored Procedure sp_Update_StartDate, which requires the parameters @StartDate and @ID to be passed to it (I’ve assumed that the variables gStartDate and gID contain the correct values for the parameters):

To execute the same command, building your SQL statement on the fly, you’d call the routine like this:

Creating Recordsets

The function works well for stored procedures and SQL statements that don’t return records. A similar procedure can be used to work with stored procedures that return Recordsets. The following routine returns a Recordset created by a stored procedure:

To use this routine, call it while passing your stored procedure name, the name of your database server, the name of the database, and a Boolean value indicating whether you want to use SQL Server’s trusted security.

Handling identity

One problem I’ve experienced involved retrieving the primary key when a record was added to the database. If you’re using an AutoNumber field as your primary key in an Access database, you don’t know what a record’s key is until the record is added. You need this key to create records that use its value as a foreign key. In SQL Server, the equivalent functionality is provided by the Identity column. The following function can be used to insert new records into a SQL Server 7.0 table and return the key of the new record. This function assumes that you are running a stored procedure that returns the @@Identity value:

As an example, this code inserts a new record by executing the Stored Procedure sp_Insert_StartDate, passing the variable @StartDate, and returning the Identity field in the SQL Server 7.0 table:

Using Views

An alternative to using stored procedures is to use SQL Server views. Views are created in the SQL Server database and can be used by an MS Access application to present data. As with a stored procedure, all of the view’s processing happens on the server. Access treats a SQL Server view as a linked table when attached. This code deletes any existing views bearing the name that you want to use, then creates a new SQL Server view from Access code:

To use this routine, just call it while passing the name of your view and the SQL statement to be used in the view:

I hope you’ll find these routines as useful as I have. I’ve included them in the database in this month’s download. These routines should make it easier to transition an Access-only application to an Access-SQL Server application, taking advantage of SQL Server 7.0’s power and scalability.

Sidebar: DSN-Less Connections

In these sample routines, the variable gstrConnect is a global variable that holds the connection string for your database. When connecting to a remote database like SQL Server, you must provide enough information for Jet to find the database. You can do this by setting up a DSN in the ODBC driver manager in the Windows Control Panel. This has the advantage of putting all the connection information under a single name. If you want to change your connection information for a DSN, you can do it in the Control Panel, thereby automatically updating every application that uses that DSN. You can also pass all of the connection information at the time that you open the database, creating a “DSN-Less Connection,” as I do in the examples in this article. Using a connection string eliminates the need for you to create DSNs for every computer on which you install your application.

Typically, a connection string will contain a section like “ODBC;DRIVER=SQL Server;SERVER=my-sqlsvr;UID=name;PWD=;WSID=anywsid;DATABASE=mydb.” In this example, my-sqlsvr is the name of the computer on the network running your database engine, name is a valid login ID, anywsid is any valid workstation ID, and mydb is the name of the database with which you want to work. The easiest way to get the correct connection string is to read it from the Connect property of a table in the remote database that’s linked to your Access MDB, like this:

Sidebar: Foundation Concepts

The main problem with developing client/server applications via Microsoft Access is that all queries and updates are processed by the Jet database engine. When your data is stored in a Jet database, this makes sense. But when your data is stored in a SQL Server database, which has its own database engine, Jet just adds another level of processing. So, unless you’re making use of Jet’s ability to process tables from several different databases in a single SQL query, you’ll want to use PassThrough queries that bypass Jet. Another good technique is to move your queries to SQL Server, where they can be saved as stored procedures. Like an Access query, a stored procedure is completely compiled and ready to run. Also like an Access query, a stored procedure can accept parameters to make it more flexible. More importantly, all of a stored procedure’s processing takes place on the database server, bypassing Jet.

 

Other Related Pages

Migrating Access (and Outlook Data) to SQL Server

Access Answers: Client/Server Issues

 

 

This entry was posted in Old Material. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.