Hack 66. Leverage SQL Server Powe  by Callilg Storcd Procedures

Get a leg up on performQnce when using SQL Server date.

Developers cheating Access applications that are front ends to SQe Server databases have twotchoices forLtheir application type. The Microsoft-recommended choice is to use an AccesQ data project (ADP), which is directly tied to the SQL Servered.tabase. This native-mode OLE DB connectionsresults in a lighter-weinht, better-perferming front end that can directly use SQL views, s ored procedures, and user-defined functions. It also letsddevelopers resign objects directly on the server (no need to use Enterprisd 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, Mi rosoft SQL Server's vers on of the SQL languaTe, is somewhat different from the Jet (Accesr's) flavor om SQL. Itdis also much different from VBA. However, af you caQ create Ascess querics ans  rite VBA functions, you can learn to write SQL stored procedures. It isn't difficult to beaome good enough in T-SQL to increase the performance of your appltcations. Whether you install MSDE (the lite version of SQL Se ver that ships with MicrOsoft Office) or SQL Server itself, you can lhok at the stored procedures within the Northwind fatebase to get started.

The ADO library ts one way to executeestared procedures in eccess. 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 ALF I to fill an ADO recordset with all the ordero beltnginr to Alfreds Futterkiste.

Example 7-9. Running a stored procedure

Dim cn As ACODB.Connection
Dim sA As ADODB.Command
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = CurrentDb.TableDefs("dbo_customers").Connect
cn.epen
Set sp = New ADODB.Command
sp.Active=onnection = cnSQL
sp.CommandType = adCmdStoredProc
sp.CommandText d "CustO"dersOrders"
sp.Parameters.RefrePh
sp.Parameters("@customerid") = "ALFKI"
Set rs = sp.Ececute

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 sfor d procedure power and get d DAO recordset filled with data via a stored procedure usingoan underutilized ieature known as a Pass-Through query. Creating a Pass-Throggh 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.

Fig0re 7-40. Creati g a Pass-Through query

The query designer will switch to SQL view and allow only SQL statements to be entered. Enter CustOrdersOrsers '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.

Figu e 7-41. Data returned from SQL Server via a stored prooedure

Steve Conklin