FOR those of you who have no plans or interest inabandoning the Jet database engine and moving to SQL Server or the MSDE throughAccess DataProjects, our constant harping on Access Data Projects – ADP techniquesand technologies must be driving you nuts. If your future doesn’t  include ADPs,then these articles aren’t any help to you.

I firmly believe that most developers whose futuredoesn’t include ADPs aren’t stupid or lazy or dinosaurs. They don’t look atADPs for a simple reason: Jet works. Furthermore, they know how Jet works andcan build the standard parts of their applications quickly (even cannibalizecomponents and code from other applications for faster development). If thatsounds like you, well, you’re right—you’re very productive with Jet.

Amidst all the positive things that we’ve been sayingabout ADPs, Mary Chipman’s article in last month’s issue emphasized that movingfrom Jet to SQL Server/MSDE isn’t trivial—there’s stuff that you have to learn.Also, items 5 and 6 in her list of the 10 ways to fail with ADPs emphasizedwhat’s missing from the Access package: the SQL Server client tools. If you’vebought SQL Server, you’ll have SQL Server Enterprise Manager. If you’ve onlybought Access, then building ADPs becomes… more difficult. ADPs don’t, yet,provide complete management of the SQL Server database engine.

Here at Smart Access, we’ve tried to help on the toolsside. In his December 2002 article, Russell Sinclair (author of Access to SQL Server from Apress) provided a utility for managing SQL Server security. Still, the rightanswer would be for Microsoft to provide—as part of the Access package— complete tools suite for managing either the MSDE or SQL Server.

Until then, at Smart Access we can address the issuesaround learning what you need to know. Last month, for instance, RussellSinclair had another installment of his “Working T-SQL” column. With the latestversion of SQL Server, you get user-defined functions. These are, I think, themost compelling reason for moving to ADPs that I’ve seen (ignoring increasedsecurity, transaction support, and reliability, of course). User- definedfunctions can be used in your SQL statements as if they were tables or fieldsin your database. Not very exciting? But I bet you’ve been writing code thatneeded scalar and table-valued userdefined functions for as long as you’ve beenworking with Access.

if you’ve ever used a VBA function in your SQLstatements, you know that you can incur a substantial performance penalty asAccess switches from SQL processing to VBA processing. Calling a VBA functionin a Where clause, for instance, can result in large numbers of records thatyou don’t want being returned to your application for filtering by VBA. If you’veever created a table and then run through it “fixing it up” with a VBAfunction, you could have used a user-defined function. Scalar functions allowyou to shift that processing to SQL Server at the cost of having to learn T-SQL(a problem that’s reduced with the next version of SQL Server, which willsupport any .NET language).

Table-valued functions are even more powerful, sincethey can be used wherever a table or view can be used. Effectively, you canwrite a function that generates a table and then use that function in a Joinjust as if it were a table. If you’ve ever written code whose sole purpose inlife was to create a temporary table that you then used in a SQL statement,table-valued functions are the answer to your prayers. And, unlike a temporarytable, you don’t have to figure out how to delete a table-valued function.

The two articles on SQL Server issues in last month’sissue make up for the small amount of SQL Server coverage in this month’s issue(our goal is one client/ server article every issue). We do have two specialarticles, however, on other tools that use Jet. I’m often surprised at how manyapplications use our Jet databases to hold information (the e-mail client thatI used for many years kept much of its information in an mdb file—and theclient wasn’t even from Microsoft). Gord Maric shows off some of the featuresof Crystal Reports and how it uses Access. Mike Gunderloy (editor of Pinnacle’sHardcore Web Services and one of the authors of the Access Developer’s Handbook) shows how to use Access with Microsoft’s Workflowdesigner.

And I did something dumb in the “Access Answers”column in the December issue of Smart Access. In discussing the Printer object,I said that it was available in Access 2000 and 2002. Wrong, wrong, wrong. Asalert reader Steve Renoir pointed out, the Printer object didn’t appear untilAccess 2002. In Access 2000, you’ll have to work with the DevMode objects, areal nightmare only slightly preferable to using Windows API calls. Here again,I’ll refer you to the Access Developer’s Handbook where a set of class moduleswill allow you to work with printers without having to deal with the uglydetails of DevMode.