Last edit: 05-05-28 Graham Wideman
|Delphi Database Notes
Article created: 98-09-23
2005-05-27: It's been many years since I last updated this page... but for the benefit of those few readers who might stumble in here, I'm throwing out all the old rubbish, and revising it to focus upon just two Delphi-and-database products that have become mainstays of my software life.
There's more than one way to access an MS Access database (xxx.mdb file) from Delphi, but my happy choice for the last seven years or so has been the DAO Database collection from Winsoft:
Winsoft web site
Readers might wonder why this venerable DAO stuff is even relevant any more -- perhaps even why Access is related to Delphi.
The key is this. Delphi is great for building apps on a variety of scales, but one scale that it's particularly brilliant for is the kind of project that one person can whip together in a few days or a week or two. Often it's corporate glue built under the corporate radar. And deployment has to be on the same level of hassle -- ie: little to none, and this is where Delphi's ability to create a relatively compact all-in-one executable, requiring no special installation, is a great match. VB6 and the more recent .NET environments certainly have their place, but simple low-hassle deployment is not one of their strongest features.
Meanwhile, MS Access is a database environment with a complementary spirit -- the orderliness and discipline of a relational database all in one file plus the power of SQL all in a personal or small-group scale environment that can be used in a range of ways from ad hoc to strict and formal.
So it's natural that a Delphi app harnessing Jet is a great match (Jet is Access's underlying but independent database engine -- ie: some DLLs that can read and write mdb files, process SQL and so on) .
The Delphi+Jet app can present mdb files to users as just another kind of document file, while enjoying the productivity of interacting with the content of the file as a database. And the Delphi app is immediately complemented by users' ability to employ the app's data in Access, so as to derive their own value by way of whatever they would like to do in Access.
Deployment for the Delphi app is as easy as usual (possibly just the exe file) -- Jet is already installed with MS Office whether or not Access is installed, and if you can't count on that then you can create a simple installation (eg: Installshield) and include MS's MDAC kit that includes Jet.
There are several ways that Delphi could access an mdb database. One is via ODBC, but that requires setting up an ODBC data source on the user's machine, and that's enough of a chore that working with mdb files is no longer as simple as just working with regular files.
A second way to work with Access files is via ADO (Active Data Objects). This is a more recent technology that provides a more general set of functions that are similar across Jet, Oracle, SQL Server and so on.
But if you just want to deal with Jet, then DAO (Data Access Objects) is the API to use because it's more specific to Jet. (Some of the functionality missing from ADO was added in ADOX.... but I'm persuaded that DAO is more direct, hence less to trip over).
Like any COM technology, you could talk to DAO direct from Delphi, but in Delphi we'd really like an interface that works like TDataset, TTable and so on..
That's where Winsoft's DAO Database Collection comes in. "Just" a set of components that work directly with Delphi's normal VCL approach to data access components, and hence hooks right in to data-aware components.
I've used this setup for many years, on quickie personal projects, departmental projects, commercial data collection software and so on.
If like me you have actual line-of-business work to complete, and you're in and out of Access on a frequent basis, then this component in your Delphi arsenal is essential. It is one of the brighter lights in the Delphi universe that makes programming predictable and enjoyable rather than onerous.
You can probably get a long way with these components without reading much of the docs, since they work more or less like Delphi's own TTable etc. But to really take advantage of the fact that you're dealing with a useful hybrid that's simultaneously file AND database you'll want to use some of the DAO features and for that you will most likely need to find and read MS's DAO helpfile which details much that that relates to these components. Furthermore, if you are into creating blobs of functionality in the Access world you may well already be using DAO in Access VBA, so much will seem familiar.
It's also worth mentioning that over these seven years, I've emailed support questions on a number of occasions, and always received a prompt and comprehensive reply. (I almost hesitate to make a comment like this because I sense that Winsoft is a fairly modest-sized operation without an army of first-level tech support -- just decent components at affordable prices. I haven't peppered Winsoft with questions (at least in my opinion :-), and I don't need hand-holding, so "succinct and quick" is fine for me.)
I think this is the spot where I say "thank you Erik Salaj!"
After drooling over this component suite for a couple of years, I finally bought it a year ago... and it was well worth it. This data-aware grid has a fantastic amount of useful functionality... in fact one of its shortcomings is that it's a little daunting to learn its structure, and how to turn all the features on and off. The docs are not bad though, and there's lots of examples.
The original purchase decision was driven by one particular project, requiring debate as to whether it was worth it for that application. It turned out to be worth it not just for the functionality that we knew we needed, but also for the additional functionality that we might have thought superfluous, but which greatly added value to the application -- notably the grid's very intuitive sorting and grouping functions. The filtering also is very useful, and only gets mentioned second because filtering is intrinsically a slight learning curve for users and so not such an instant "wow".
Since that first project, with EQG on hand, of course it was the default choice for several other projects. But on top of that, just having the component available, it really enabled some other quick but useful projects to be tackled that otherwise would have been either difficult to build or awkward to use, and thus likely would not have been worth doing.
Having tried a few grids, this one has been a very happy combination of extensive functionality, excellent stability and great end-user appeal. As I noted, the slight downside is the amount of detail you encounter in trying to get it set up... but the reward is that the quest to understand the detail really does lead to functionality that behaves sensibly and is worth turning on.
Answers to technical questions emailed to Developer Express support have been quick and to the point. There's also a support newsgroup which seems fairly active (and comments give a good sense regarding level of satisfaction).