Stored Procedure tricks

<< Click to Display Table of Contents >>

Navigation:  SQL Server and Access >

Stored Procedure tricks

Michael McManus        

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:

Function ExecuteSPT(strSQL As String) As Boolean

On Error GoTo spt_err

Dim qdf_SP As QueryDef

Dim dbs As Database

Set dbs = CurrentDB()

Set qdf_SP = dbs.CreateQueryDef("")

qdf_SP.ReturnsRecords = False

qdf_SP.Connect = gstrConnect

qdf_SP.SQL = strSQL

qdf_SP.ODBCTimeout = 15

qdf_SP.Execute

qdf_SP.Close

 

spt_exit:

  ExecuteSPT = True

  Exit Function

 

spt_err:

  ExecuteSPT = False

End Function

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):

Dim sp_sql as string

sp_sql = "Execute sp_Update_StartDate " & _

      gStartDate &", "& gID

If Not ExecuteSpt(Sp_sql) Then

  '…error handling code

End If

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

Dim sp_sql as string

sp_sql = "UPDATE tblStartDates set [StartDate] = '" _

      & gStartDate &"' WHERE [ID] = " & gID

If Not ExecuteSpt(Sp_sql) Then

  '…error handling code

End If

 

 

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:

Function CreateRecordset(strSQL As String) _

               as Recordset

Dim qdf_SP As QueryDef

Dim dbs As Database

 

Set dbs = CurrentDB()

Set qdf_SP  = db.CreateQueryDef("")

qdf_SP.Connect = gstrConnect

qdf_SP.ReturnsRecords = True

qdf_SP.ODBCTimeout = 15

qdf_SP.SQL = strSQL

Set CreateRecordset = qdf_SP.OpenRecordset

Qdf_SP.close

End Function

The following code uses a similar technique for ADO Recordsets:

Function CreateADORecordset(strSQL As String, _

        strServer As String, strDBName as string, _

        bolTrusted As Boolean) As ADODB.Recordset

Dim ado_Conn As ADODB.Connection

Dim ado_Rec As ADODB.Recordset

 

Set ado_Conn = New ADODB.Connection

ado_Conn.ConnectionTimeout = 15

ado_Conn.Provider = "SQLOLEDB"

ado_Conn.Properties("Data Source").value _

               = strServer

ado_Conn.Properties("Initial Catalog").value _

               = strDBName

 

If bolTrusted Then

  'Use this for trusted connections

  ado_Conn.Properties("Integrated Security") _

        .value = "SSPI"

Else

  'Use this for SQL Security

ado_Conn.Properties("User ID").value = "myUID"

ado_Conn.Properties("PassWord").value = "myPassWord"

End If

 

ado_Conn.Open

 

Set ado_Rec = New ADODB.Recordset

 

ADO_Rec.Source = strSQL

Set ADO_Rec.ActiveConnection = ado_Conn

ADO_Rec.Open

Set CreateADORecordset = ADO_Rec

End Function

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:

Function ReturnValue(sp_name As String)

Dim rs_sp As Recordset

Dim qdf As QueryDef

Dim ret As Long

Dim dbs As Database

 

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("")

qdf.Connect = gstrconnect

qdf.ReturnsRecords = True

qdf.ODBCTimeout = 15

 

qdf.SQL = "declare @retval int " & _

"declare @val int " & _

"execute @retval = " & _

       sp_name & " @val output " & _

       "select @val as x"

qdf.SQL = SP_SQL

Set rs_sp = qdf.OpenRecordset

 

ret = rs_sp![X]

ReturnValue = ret

rs_sp.Close

End Function

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:

Dim sp_sql as string

sp_sql = "sp_Insert_StartDate " & gStartDate 

If Not ExecuteSpt(Sp_sql) Then

  Goto ErrorHandler

End If

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:

Sub CreateView(strName as String, strSQL As String)

Dim strSQLPass As String

strSQLPass = _

 "If exists (select * from sysobjects where id =" & _

 " object_id(N'[dbo].[" & strName & "]') and " & _

 "OBJECTPROPERTY(id, N'IsView') = 1) " & _

 " drop view [dbo].[" & strName & "] "

Call ExecuteSPT(strSQLPass)

 

strSQLPass = "create view " & strName & " as " & _

  strSQL

Call ExecuteSPT(strSQLPass)

End Sub

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

CreateView "MyView", "Select Field1 From Table1"

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.

 

 

Your download file is called  004McManus.ZIP   in the file SA200004_down.zip

This can be purchased with all the other downloads on this page

 

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:

Dim dbs as database

Dim tblDef As TableDef

Set dbs = CurrentDb()

Set tblDef = dbs.TableDefs("tblMyTable")

gstrConnect = tblDef.Connect

 

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