FOR those of you who have no plans or interest in abandoning the Jet database engine and moving to SQL Server or the MSDE through Access DataProjects, our constant harping on Access Data Projects - ADP techniques and 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 future doesn’t include ADPs aren’t stupid or lazy or dinosaurs. They don’t look at ADPs for a simple reason: Jet works. Furthermore, they know how Jet works and can build the standard parts of their applications quickly (even cannibalize components and code from other applications for faster development). If that sounds like you, well, you’re right—you’re very productive with Jet.
Amidst all the positive things that we’ve been saying about ADPs, Mary Chipman’s article in last month’s issue emphasized that moving from 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 emphasized what’s missing from the Access package: the SQL Server client tools. If you’ve bought SQL Server, you’ll have SQL Server Enterprise Manager. If you’ve only bought 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 tools side. 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 right answer would be for Microsoft to provide—as part of the Access package—a complete tools suite for managing either the MSDE or SQL Server.
Until then, at Smart Access we can address the issues around learning what you need to know. Last month, for instance, Russell Sinclair had another installment of his “Working T-SQL” column. With the latest version of SQL Server, you get user-defined functions. These are, I think, the most compelling reason for moving to ADPs that I’ve seen (ignoring increased security, transaction support, and reliability, of course). User-defined functions can be used in your SQL statements as if they were tables or fields in your database. Not very exciting? But I bet you’ve been writing code that needed scalar and table-valued userdefined functions for as long as you’ve been working with Access.
If you’ve ever used a VBA function in your SQL statements, you know that you can incur a substantial performance penalty as Access switches from SQL processing to VBA processing. Calling a VBA function in a Where clause, for instance, can result in large numbers of records that you don’t want being returned to your application for filtering by VBA. If you’ve ever created a table and then run through it “fixing it up” with a VBA function, you could have used a user-defined function. Scalar functions allow you 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 will support any .NET language).
Table-valued functions are even more powerful, since they can be used wherever a table or view can be used. Effectively, you can write a function that generates a table and then use that function in a Join just as if it were a table. If you’ve ever written code whose sole purpose in life 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 temporary table, you don’t have to figure out how to delete a table-valued function.
The two articles on SQL Server issues in last month’s issue 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 special articles, however, on other tools that use Jet. I’m often surprised at how many applications use our Jet databases to hold information (the e-mail client that I used for many years kept much of its information in an mdb file—and the client wasn’t even from Microsoft). Gord Maric shows off some of the features of Crystal Reports and how it uses Access. Mike Gunderloy (editor of Pinnacle’s Hardcore Web Services and one of the authors of the Access Developer’s Handbook) shows how to use Access with Microsoft’s Workflow designer.
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. As alert reader Steve Renoir pointed out, the Printer object didn’t appear until Access 2002. In Access 2000, you’ll have to work with the DevMode objects, a real 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 modules will allow you to work with printers without having to deal with the ugly details of DevMode.