Hack 66. Leverage SQL Server Power by Calling Stored Procedures

Get a leg up on performance when using SQL Server data.

Developers creating Access applications that are front ends to SQL Server databases have two choices for their application type. The Microsoft-recommended choice is to use an Access data project (ADP), which is directly tied to the SQL Server database. This native-mode OLE DB connection results in a lighter-weight, better-performing front end that can directly use SQL views, stored procedures, and user-defined functions. It also lets developers design objects directly on the server (no need to use Enterprise Manager).

Despite these advantages, many situations force developers to use ODBC linked tables in a traditional Access MDB file. Not the least of these is the ability to create local tables in the MDB (in an ADP, even the Switchboard Items table must be on the server) and the ability to connect to other data sources (such as other Access databases, Excel spreadsheets, text files, and so on). Just because you choose to use an MDB as a front end doesn't mean you have to give up the server-side processing power of SQL Server stored procedures.

7.9.1. Hooking Up with ODBC

When an Access MDB is using ODBC links to SQL Server, all data processing is done on the client sidethat is, within Access on the workstation. If a listbox on a form gets filtered by a combo box selection, all the records are returned over the network to Access and Access applies the filter. Alternatively, the use of stored procedures can increase performance in your Access MDBs by shifting the filtering to the server. Stored procedures are powerful because they combine the data-joining capabilities of Access queries or SQL views with the ability of VBA procedures to accept parameters and to loop and process data.

T-SQL, Microsoft SQL Server's version of the SQL language, is somewhat different from the Jet (Access's) flavor of SQL. It is also much different from VBA. However, if you can create Access queries and write VBA functions, you can learn to write SQL stored procedures. It isn't difficult to become good enough in T-SQL to increase the performance of your applications. Whether you install MSDE (the lite version of SQL Server that ships with Microsoft Office) or SQL Server itself, you can look at the stored procedures within the Northwind database to get started.

The ADO library is one way to execute stored procedures in Access. You do this in VBA by executing a Command object whose command text is the stored procedure name. First it is necessary to open a Connection object on the SQL Server database. The code in Example 7-9 executes the CustOrdersOrders stored procedure that ships with Northwind, sending in the much-abused customerid ALFKI to fill an ADO recordset with all the orders belonging to Alfreds Futterkiste.

Example 7-9. Running a stored procedure

Dim cn As ADODB.Connection
Dim sp As ADODB.Command
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = CurrentDb.TableDefs("dbo_customers").Connect
cn.Open
Set sp = New ADODB.Command
sp.ActiveConnection = cnSQL
sp.CommandType = adCmdStoredProc
sp.CommandText = "CustOrdersOrders"
sp.Parameters.Refresh
sp.Parameters("@customerid") = "ALFKI"
Set rs = sp.Execute

Access, however, can't use ADO recordsets in certain situations. Although Access uses ADO more and more with every new version release, Access 2003 still has deep ties to DAO, so much so that Microsoft put back a default reference to DAO in VBA, after not including it in Access 2002 (XP). A data-entry form bound to a linked table will have an underlying recordset that isn't ADO, but rather, is DAO. Controls such as combo boxes or listboxes, on unbound or DAO-bound forms, require their recordsets to be DAO as well.

7.9.2. Creating a Pass-Through Query

Access can tap into stored procedure power and get a DAO recordset filled with data via a stored procedure using an underutilized feature known as a Pass-Through query. Creating a Pass-Through query is relatively straightforward, and the results returned are in a DAO recordset, appropriate for use in any Access object or control that can use a query as its data source.

To create a Pass-Through query, select Queries in the Database window, and click New. Click Design View, and then click OK. Click Close on the Table list to go directly into Design view. On the Query menu, click SQL-Specific, and then click Pass-Through, as shown in Figure 7-40.

Figure 7-40. Creating a Pass-Through query

The query designer will switch to SQL view and allow only SQL statements to be entered. Enter CustOrdersOrders 'ALFKI' in the SQL view of the query designer. Click Save, and name the query qry_CustOrdersOrders_pt.

At this point, Access doesn't know where to pass this query. On first execution, you are prompted for the data source connection to use: cChoose the same data source you used to link your SQL tables. After choosing the appropriate data source, Access sends the SQL string contained in the query to the server, and SQL runs the stored procedure and returns the results to Access, as shown in Figure 7-41.

Figure 7-41. Data returned from SQL Server via a stored procedure

Steve Conklin