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.
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
