Access Hacks

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.

Hooking Up with ODBC

When an Access MDB is using ODBC links to SQL Server, all data processing is done on the client side—that 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, ...