David said:
Well, the fact that they didn't seem to be able to make ADPs work
reliably after 3 major attempts says to me that there might have
been something fundamentally flawed in the underlying design.
People keep telling me about how ADPs don't work, and yet I've been using
one as my main project at work for 3 or 4 years now, and I haven't found any
problems in any of the 200x line that I couldn't work around, usually fairly
trivially. It makes me wonder if it's a case of different design
philosophies working better or worse for ADPs, and I just lucked out and
happened to use one that worked better.
You can still use an ADP for design and management, though I prefer
using the SQL Server tools directly.
Agreed. About the only thing I regularly use ADP for in terms of day-to-day
back-end work is its ability to execute SPs directly and prompt you for
parameters without you having to create the SQL statement and remember the
parameters yourself. It's certainly not a feature I'd jump to ADPs just to
use, but it's very convenient, I must say.
Well, I never saw the benefit of ADPs in the first place, to be
honest, so I never used them.
Easily the single largest benefit I find is the ability to see nearly all
the SQL Server objects in real-time without having to worry about
re-linking, maintaining lists of the names of any tables or views you want
to use on the Access side as well as creating the actual objects on the SQL
Server side, etc. They're simply THERE. No fuss, no muss.
You could probably create a similar functionality from an MDB or ACCDB, but
I'd imagine this would involve some fairly hefty coding, inter-operating
with SQLDMO, and of course the slowness of not only querying for what the
objects are, like an ADP would, but then adding to the amount of data on the
wire by creating or re-creating links for them.
Part of that is because I was dealing
with existing apps that got upsized, rather than freshly developed
apps that were created for the first time after ADPs were a
possibility.
So am I. This app was designed from the ground up originally as a split
database with both ends in Access, and all code written for DAO. As I began
to see that replication was causing immense nightmares and that this really
would be better off as a centralized SQL Server database, I began the switch
to ADO. While slower, the feature-set was far better and easier to work
with...particularly when it comes to things like filtering and sorting
(though there certainly are a few limitations there as well).
Once I'd brought everything over to SQL Server, I played briefly with both
an MDB and an ADP design and decided that for what I was doing, ADPs were
absolutely the way to go, and I've never looked back. No more linking, much
faster overall (since there's absolutely NO chance of queries being done on
the front-end, and there's no need to fiddle with fake indices and other
such silly MDB-style optimizations), and almost all my ADP objects were
visible to me dynamically, which was great for ad-hoc queries...I designed
them, then sent a popup to the person involved. No need to modify a list of
objects to be linked, as the code could simply look for all views/SPs
objects with a certain pattern.
But I was always turned off by the "ADPs don't use
Jet!" religion, as the idea that there was something virtuous in
bypassing Jet never made any sense to me.
That part was irrelevant to me as well. There's nothing virtuous in any
particular use or non-use of a technology; the question in the end is speed
and functionality, regardless of how it's accomplished.
A few? There are different bugs in each of the 3 ADP versions (2000,
2002, 2003), and things that were broken in 2000 that were fixed in
2002 and rebroken in 2003, and things that worked in 2000 that were
broken in 2002/2003. What a mess!
As I said above, I've never had any MAJOR problems. There have certainly
been some nuisances along the way, forms that didn't display data properly
or wouldn't allow updates when they should, etc., but nothing that couldn't
be solved.
And then there's the whole mess
with ADO guessing wrongly about how to execute your SQL, and, well,
it just seems to me like a major problem.
Actually, my impression is that it's not ADO doing the guessing, but some
strange desire on the part of the ADP engine to make guesses when there
shouldn't have been a need to do so. I never really understood the logic of
re-interpreting SPs and the like so that the ADP could write to the tables
directly, rather than just letting the SPs do what they were designed to do!
In the end, I think THAT has caused the most problems of all, and it's why
all my forms are based on tables, views, or dynamically-generated SQL
statements rather than EVER using an SP as a record source. Still a few
headaches, to be sure, but nothing all that noticeable in the long run.
Microsoft still recommends ADPs as a great reporting platform,
though.
Which is funny, because I actually have very few Reports (with a deliberate
capital "R") in my ADP. Most of what I'm doing is exporting Views to Excel
templates, or compiling data into RTF files.
Er, some of us didn't swallow the Kool Aid, because it was obvious
that it was promoting an MS agenda that didn't fit well with Access
development. I, for one, never used ADO, because most of my apps
were Jet-based. This served me well, as by the time I got to the
point where I really needed ADO, it was already obsolete!
Hahaha...no, I didn't swallow the Kool Aid either. Microsoft was
recommending the MSDE/ADP combo for years before I even gave it my first
look. And if I weren't using a full-fledged server-based SQL Server
database, I would never have even looked at an ADP. I most certainly
wouldn't design anything local in MSDE...it'd be Jet all the way. In fact,
all of my home apps are just that; it's only at work that I use ADPs.
Michael Kaplan escoriated Microsoft in print for the half-baked
enterprise additions to Access 2000. Perhaps that's why I was
suspicious, because I had read someone speak up about the problems
at the time they were released. Michael paid a price for that in his
relations with Microsoft (though they've obviously patched things up
since), but he saved a lot of people a huge amount of grief, seems
to me.
Well, maybe that's why I looked at ADPs then. I think Michael Kaplan is a
supreme jerk who's only managed to gain the popularity he has because of his
technical knowledge. I find it hard to read through his escoriations of
anything and everything that he didn't design personally, so I usually make
a point of ignoring anything he says unless someone whom I actually respect
has recommended it. So far, in the last 7 or 8 years, I think I've met two
people (including yourself) who've actually recommended reading something
he's written. (And the other one was so long ago, it might well have been
you that first time as well! <LOL>)
This is not to say that he was wrong, and in fact I wouldn't be at all
surprised if he was right...just that I most definitely don't hang on his
every word (or any word, for that matter).
Rob