Review 

Diamond Access - a Gem for the Jet Set.

There's only one way to do Jet.  Microsoft Jet, that is, a.k.a. Access databases. And that's to let the Masters do it.  It's a tough job.  Distributed caching, pre-compiled Queries, Wide Area Network synchronization, locking placed megabytes beyond the end of the file, and somehow it all works like a charm, multi-user across a network.  100 MB file-based databases that actually work with 20 or more users on-line?  Yes, Jet can do that, routinely, if you're willing to learn a new trick or two.

But there was, until recently, only one way to really take advantage of it, and that was to use native Microsoft tools.  Everybody tried to equal VB in Jet performance, and failed.  Borland's BDE was hammered senseless into a Jet driver: it shook, it quivered, and still it sucked.

And so, I thought, why not take advantage of the COM interface that Jet exposes through Data Access Objects (DAO3.5), and speak to it on its own terms? Good project! But somebody in metropolitan Chelyabinsk had already done it. Now in case you don't know about Chelyabinsk, you're not alone. A multi-million inhabitant steel and silicon valley that until recently did not even officially exist, on maps or otherwise, the home of the Soviet Military Industrial Complex. Gary Powers got shot down trying to take home some Chelyabinsk postcards from his U2. They got some smart people there, and some of them use Delphi. Anyway, as I was saying, yes, a COM object. That's basically what Diamond Access is.  On the Delphi side, by hooking into the TDataSet hierarchy, it acts enough like BDE that the data bound controls work, and the learning curve is almost zero - it boils down to knowing a few (useful) Jet-specific things, if you want to really sizzle.

It's a no-brainer creating simple Jet database apps with Diamond, it's just like with BDE, but with no BDE headaches, no huge redundant distribution, no aliases to manage. Feels so good! My Delphi IDE suddenly took on a whole new shine.

Unlike some other BDE replacements, Diamond requires no special grids or other controls, since it presents as:

• TDAODatabase
• TDAOQuery
• TDAOTable
• TDAOQueryDef

You use these in place of  the corresponding BDE data components. Like, doh, it just drops in. You can use the same TDataSources and controls, so porting a GUI app from BDE to Diamond can be done in 5 minutes by editing your DataModules. Or you can use in-line code, DAO is a very rich object. You statically link everything in, or use packages, and then you need NO stinkin' BDE redistributables. You just have to include the licensed DAO distribution (mainly DAO350.DLL), which means you should own a Microsoft tool. Maybe something like VB Proletarian Edition, to create your splash screens with, and thus add the "primary or significant functionality" to your project that entitles you to distribute DAO. Read the Microsoft License agreements, be creative.

We are still in the midst of development on the project that uses Diamond, but so far so good.  The design uses a lot of executables that link together as out-of-process COM/DCOM objects, a.k.a Automation/OLE ActiveX.  Some are written in VC++, others in VB5, and a third group in Delphi 3.  One does database reads and handles serial ports to load assignments into portables the size of a pack of cigarettes.   Another is a sort of application server that sends screens over radio to these handhelds, and performs database queries on behalf of each wireless.  There are also POP3 Clients, SMTP Servers, little utilities, viewers, altogether it creates a frightful freight tracking system for the transportation industry

The stuff written in Delphi consists primarily of utilities, some of which must talk to Jet databases.  Diamond to the rescue!

Our "field" experience with Diamond (v 1.33) has been very good. The author quickly corrected a problem in 1.31 where QueryDefs with many (e.g. 60) fields took a long time to complete. Now the Delphi code is as fast as the VB or VC++ equivalent, which means very fast, typical queries take like 200 mS on medium tables over a LAN. This compared to well over a second on even trivial Access queries using the Borland Access BDE 4.51 driver.   On bigger queries the difference is startling.  On QueryDefs, well, even more so, as BDE usually locks up and goes to heaven.  Given Diamond, there is simply no reason I can see to use the Borland Access driver, which features all manner of access ;-) violations and incompatibilites as soon as one pushes the envelope. I recognize that if one is using the MIDAS stuff, it's not necessarily such a simple matter, but that's another story.

We have not by any means fully tested all of Diamond's capabilities, so your mileage may vary, as we are fixated on parametrized QueryDefs. These allow precompiled SQL statements to be stored in the Jet database. Unfortunately BDE only partially recognizes QueryDefs in Jet. In practice these are just like Stored Procedures, they seem to be the best-performing way to access Jet data, and it is important (to us) that Diamond handles them well. By creating a set of QueryDefs at startup in code, an app can enjoy the performance benefits, without the programmer having to pre-create the QueryDefs using the MSAccess GUI. Access is still one of the best tools for designing them, testing, viewing data, and laying out the relations. In any case, we are QueryDef freaks that had a pressing job to do, and in this area Diamond has done very well.

Superficial testing of other areas showed consistently good performance, no surprises.   There are some little things to gripe about. The 3 little examples supplied could certainly use expansion. To get full access to all of DAO's methods/properties, the source version of Diamond is required, as some are declared private by default.  The author says it's to reduce confusion, but I would prefer a wholesale public mapping.  You'd be silly not to get source anyway. Minor quibbles. The stuff works great. We are confident that if problems crop up, either the author will fix them, or we will.  In contrast we just tried using a parametrized BDE TStoredProc with a Jet database using Delphi 4, and it it's still generating Access Violations, locking up the IDE, somewhere deep in megabytes of BDE code to which we have no source.

The Diamond author is responsive, and implements suggestions very quickly - but should he degenerate, start drinking heavily, fail to respond to threats, or tire of programming, you have the source, and it's very straightforward.  I don't think anyone should shy away from such a component because it's from a small company. If you can maintain your own spaguetti code, you can maintain this. For instance we just recompiled it ourselves for Delphi 4 with one tiny change.  Most of the implementation is concerned with converting BDE style constructs to and from Jet.  The core methods and properties of Jet are just basically exposed as COM interfaces from the DAO classes.

We purchased Diamond by credit card over a third party secure server (www.shareit.com) with source, on the basis of a specific solution to a specific problem, and at $99 with source ($49 without), it's the kind of impulse purchase every Delphi user should make now and then. Upon confirmation of payment, they sent us the full package by e-mail.  Try the trial version, then, if you like it, you can pick up a Diamond at their check-out-stand. Jet is a cool and convenient database format, and if you want to do a quick dance, you shouldn't have to drag the slobbering Boring Dinosaur Experiment to every party with you. Be generous - leave the BDE to the people who claim they like it.

Microsoft's direction in database interface is COM. ADO and the underlying OleDB are just ways to present a data store as a series of COM objects. The old call-based DAO DLL interface will be eliminated in time. Thus Diamond is probably the most logical Jet implementation for the future, as it should work with and plug right in with the next implementations from Microsoft.

By exposing the TDataSet hierarchy, Borland has mercifully made it possible to bypass its aging and Wagnerian BDE, yet retain a common database interface for screen objects. Diamond Access is probably one of the first in a series of COM-based Delphi database objects, and it's something no Delphi programmer should be without. It's a great little package.

Tony Milosz
Peakdesign
tonym@peakdesign.com
http://www.peakdesign.com 

Peakdesign is not afiliated with the the makers of Diamond Access, or with Chelyabinsk. We have  received no compensation, bakshish, or incentives for this review.