ADP Vs. ODBC

[Robert Morley]

I suspect that it's a matter of expections.

If you're an enterprise developer who had never used Access as a
front end, ADPs were probably a great step up from the tools
available before MS introduced them.

On other hand, if you were an experienced Access developer, you
likely found all sorts of inconsistencies in the implementation, as
well as many holes, i.e., things that MDBs made easy, and which ADPs
made much harder.

In my case, however, I was the opposite. I've never been an Enterprise
developer, only an Access developer. When I upsized to SQL Server, I found
that ADPs were far and away better than the clunkiness of using linked
tables and pass-through queries. Were there things that didn't work quite
like they should? Yeah, sure...but at least for me, they were few and far
between, and nothing that a little coding around couldn't solve.

That decision has served me well,
as I now have SQL Server projects that were upsized from a Jet back
end and the MDB app required only minor tweaking to get things
running well with SQL Server.

This, at least, I can agree with. Going from DAO to ADO (my first step)
required a fair bit of re-coding, then going from ADO using Index/Seek
(since I'd been misled as to the capabilities of ADO w. SQL Server) to
queries or Find/Filter was another nightmare. SOME of that, at least, would
have been eliminated with going to an ODBC model, though probably not all
that much in the end.

Then, of course, there was the change in logic involved with assuming that
all objects are local and fast vs. all objects being remote. Suffice to
say, that was a learning experience.

That switch makes no sense to me. I don't see any reason why
shouldn't have just ported the MDB to SQL Server and revised where
necessary.

Well, a large part of it was that DAO, in my experience, is a little limited
when it comes to linked tables. ADO pretty much allows you to maintain
identical code, changing only your connection string, and maybe one or two
other things (at least once I wrapped my head around the Seek/Index issue
and fixed all those up). Of course, some of the functionality of ADO is
better as well, which was a significant factor for me as well. The
functionality of Filter in DAO seems truly primitive, and the inability to
open connections and/or recordsets asynchronously is also rather annoying at
times.

Well, remember, that there's an additional layer in between the ADP
and ADO that most promoters of ADPs seem to pretend doesn't exist,
and probably that's the layer that was causing the problems (i.e.,
the attempt to bypass secured views and use the underlying tables
directly).

That's exactly what I was getting at. ADO itself seems fairly reliable,
and I don't believe it "guesses" at how to execute your SQL in any
significant way (unlike the layer behind ADPs).

As for the whole guessing game that ADPs play, as I said, I think that
should've been removed for increased reliability/stability. It's just
always seemed a little silly to me that you would deconstruct something that
presumably already works correctly, simply to access tables directly! I
mean, if a view is already alterable, what need is there to access the
table? (And if it's not, it presumably wasn't meant to be!) Bad design
choice in my books...but hey, I certainly wasn't on the design team so I
don't know what the challenges were.

I believe it was an attempt to make result sets writable that would
otherwise be read-only. I also think it's related to the whole
disconnected recordsets technology.

Yeah, and that gets back to what I said above. If a View isn't writable in
the first place, an ADP shouldn't take it upon itself to bypass it! I don't
believe ADPs use disconnected recordsets, though...at least not on the
surface. If they do so behind the scenes, then maybe they should've relied
more on ADO to do it, as I've never had any tremendous difficulties with
them via ADO.

I guess what I'm getting at is that ADPs were poorly thought out, but rather
than re-think the problem and get rid of what doesn't work to come out with
a better model, MS simply said "The 15-year old technology works well
enough. We'll just assume everybody will settle for that, or maybe give up
on the program that's designed from the get-go to be a database front end
and go with the exceptionally slow bloatware we call VB.NET". Ummm...no!

It's the views issue that I recall causing the most complaints. Of
course, I got all of it second hand, as I wasn't doing them myself.
But when someone as brilliant as Steve Jorgensen works at ADPs for
several years and then gives up on them in disgust, it's a pretty
important data point to me.

Yes, views were certainly almost as much of a problem as SPs were, but at
least in my experience, with a little trial and error, they could usually be
made to work.

As to the brilliance of any programmer, I think a lot of it is dictated by
the direction of the company and/or the managers. If they don't understand
why something can't work the way it's written, they can force the programmer
into an inappropriate design that doesn't have a hope in hell of ever
working well. I suspect that's where ADPs went, possibly combined with the
tunnel vision of "MDB vs. ADP" instead of focussing on using the best of
both worlds.

Well, I completely disagree with that. I think he's a great guy who
has enormous integrity, as well as the technical chops. That he's no
diplomat bothers me none at all (speaking as another nondiplomat!).

There's a difference between not being a diplomat and eviscerating those who
have a different viewpoint. While I've only had one such incident with him
(and it was enough), I've heard of several others (and I've heard some
suggest even MS themselves, though he addresses some of that on his website
with the "former MVP" thing) who are utterly unable to work with him.

You must hang out in different newsgroups than I do, because among
the MVPs that I know (electronically), he's very respected and
well-liked (and sorely missed in the Access newsgroups).

Possibly, although I also don't know many people who will randomly post "hey
I really love this guy who hasn't posted here in years" to a newsgroup, so
it's possible that he IS well-loved by many and I just don't know about it.
Still, I've brought up my dislike of him before on one or two occasions
in Access newsgroups and the number of people rushing to his defence has
been truly underwhelming.

To quote from his website (http://www.trigeminal.com/michka.asp, last para),
"Yet another question I am frequently asked, especially by people who see me
in the newsgroups, is why am I so angry all the time? The truth is that I am
not all that angry. I think that the reason I am not angry is because I do
not leave frustrations bottled up inside of me, I let them right out. That
and my passion for my work is often mistook as anger. In person, I can be a
real pussycat, sometimes."

Well, I admit I've never met him in person, but if one of the frequently
asked questions about you is "why are you so angry all the time" (to the
point where you feel the need to specifically address it on your website),
well, I don't think there's much more I need to say.

He had some really telling criticisms of Access 2000, particularly
ADPs and DAPs (as well as the whole ADO thing). Unfortunately, the
article is not available online so I can't point you to it.

I'm pretty sure I've read it before...it certainly sounds familiar. And
while I may not like him, and I may think ADPs need another chance (with
some admittedly significant alteration/removal of code under-the-hood), I

I think it's a terrible thing to evaluate the case made by someone
on the basis of whether you like them or not. If what they say is
well-argued and fact-based, seems to me that your personal dislike
for them should be irrelevant.

Actually, I said that I wouldn't be surprised if he was right. I may not
like him, but I acknowledge that he is more skilled than I will ever be when
it comes to databases, particularly when it comes to the behind-the-scenes code.

My point was not that I *decided* based on my opinion of him, just that I
rarely ever read his stuff *because* of my opinion of him. When I have read
his stuff, I find his facts to be generally undisputable, but his
opinionatedness is something else again. Just give me the facts and let me
draw my own conclusions about how they will affect my business decisions.

I of course read information from several other sources when I was
considering the changeover, so it's not like I made a blind decision just
because I didn't read everything he had to say about the subject.


Rob

 

[Guest]

"To take maximum advantage of the new features in Office Access 2007,
Microsoft recommends the use of linked tables to connect to SQL Server
data."

So, I may be the only one who cares, but let's be specific:

have they fixed transactions, or are linked tables still broken?

If you use linked tables to connect Access 2007 to SQL Server,
does Access create multiple connections?

If you open a transaction using linked tables in Access 2007, do
the multiple connections inside the transaction start multiple SQL
Server transactions which block each other?

(david)

 

[Guest]

I still go back and read MK's postings, even now when they
are long out of date, because he writes well and knows
what he is talking about..... but I smiled when I read Robert.'s
opinion because it's fun to see someone here who actually
remembers the old days and the old characters, and his memory
of MK was immediately recognisable.

I used to think that maybe he (MK) wasn't getting enough sleep?

(david)

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.

I suspect that it's a matter of expections.

If you're an enterprise developer who had never used Access as a
front end, ADPs were probably a great step up from the tools
available before MS introduced them.

On other hand, if you were an experienced Access developer, you
likely found all sorts of inconsistencies in the implementation, as
well as many holes, i.e., things that MDBs made easy, and which ADPs
made much harder.

I was not one of those developers, as I heard about the problems
with ADPs early on, and since I wasn't doing any SQL Server
projects, I just stuck with MDBs. That decision has served me well,
as I now have SQL Server projects that were upsized from a Jet back
end and the MDB app required only minor tweaking to get things
running well with SQL Server.

[]

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.

That switch makes no sense to me. I don't see any reason why
shouldn't have just ported the MDB to SQL Server and revised where
necessary.

[]

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.

Well, remember, that there's an additional layer in between the ADP
and ADO that most promoters of ADPs seem to pretend doesn't exist,
and probably that's the layer that was causing the problems (i.e.,
the attempt to bypass secured views and use the underlying tables
directly).

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!

I believe it was an attempt to make result sets writable that would
otherwise be read-only. I also think it's related to the whole
disconnected recordsets technology.

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.

It's the views issue that I recall causing the most complaints. Of
course, I got all of it second hand, as I wasn't doing them myself.
But when someone as brilliant as Steve Jorgensen works at ADPs for
several years and then gives up on them in disgust, it's a pretty
important data point 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.

Well, I completely disagree with that. I think he's a great guy who
has enormous integrity, as well as the technical chops. That he's no
diplomat bothers me none at all (speaking as another nondiplomat!).

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>)

You must hang out in different newsgroups than I do, because among
the MVPs that I know (electronically), he's very respected and
well-liked (and sorely missed in the Access newsgroups).

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).

He had some really telling criticisms of Access 2000, particularly
ADPs and DAPs (as well as the whole ADO thing). Unfortunately, the
article is not available online so I can't point you to it.

I think it's a terrible thing to evaluate the case made by someone
on the basis of whether you like them or not. If what they say is
well-argued and fact-based, seems to me that your personal dislike
for them should be irrelevant.

 

[David W. Fenton]

In my case, however, I was the opposite. I've never been an
Enterprise developer, only an Access developer. When I upsized to
SQL Server, I found that ADPs were far and away better than the
clunkiness of using linked tables and pass-through queries. Were
there things that didn't work quite like they should? Yeah,
sure...but at least for me, they were few and far between, and
nothing that a little coding around couldn't solve.


This, at least, I can agree with. Going from DAO to ADO (my first
step) required a fair bit of re-coding, then going from ADO using
Index/Seek (since I'd been misled as to the capabilities of ADO w.
SQL Server) to queries or Find/Filter was another nightmare. SOME
of that, at least, would have been eliminated with going to an
ODBC model, though probably not all that much in the end.

Then, of course, there was the change in logic involved with
assuming that all objects are local and fast vs. all objects being
remote. Suffice to say, that was a learning experience.


Well, a large part of it was that DAO, in my experience, is a
little limited when it comes to linked tables.

The only thing that springs to mind is SEEK, but there's hardly ever
any reason to use that in the first place (as opposed to opening a
recordset with a WHERE clause), and doesn't work except with Jet
data in the first place. So it's not something I've ever used (never
needed it), so it has never been an issue for my upsizing tasks.

I can't think of a single other thing in DAO that was problematic
with linked tables. Can you mention some of them?

ADO pretty much allows you to maintain
identical code, changing only your connection string, and maybe
one or two other things (at least once I wrapped my head around
the Seek/Index issue and fixed all those up).

The only DAO changes I've had to make is adding dbSeeChanges to the
options, and that's pretty darned easy to do with a FIND operation.

Of course, some of the functionality of ADO is
better as well, which was a significant factor for me as well.
The functionality of Filter in DAO seems truly primitive,

I don't understand this one. I've hardly ever used filtered
recordsets, except in the odd situation where I open a subset of
records, then want to operate on a subset of that subset. This is
only an issue with recordsets, seems to me, and most editing
operations should be done with SQL statements instead of by
sequentially through a recordset. In the cases where you need to
sequentially navigate a recordset, one uses a WHERE clause that
returns the smallest possiblen number of records, so I'm having some
difficulty understanding why one would often need to Filter an
existing recordset.

and the inability to
open connections and/or recordsets asynchronously is also rather
annoying at times.

Where is this an issue? It's never been one for me.

[]

I guess what I'm getting at is that ADPs were poorly thought out,
but rather than re-think the problem and get rid of what doesn't
work to come out with a better model, MS simply said "The 15-year
old technology works well enough. We'll just assume everybody
will settle for that, or maybe give up on the program that's
designed from the get-go to be a database front end and go with
the exceptionally slow bloatware we call VB.NET". Ummm...no!

I think it would have been unwise of MS to invest time and effort in
fixing ADPs to work with classic ADO, which is really obsolete now.
On the other hand, if they'd fixed ADPs to work with ADO.NET and
allowed Access to use ADO.NET in place of classic ADO, that would
have been a big win for everyone.

Maybe that's in the works?

What would you say if they made it possible to create an ACCDB that
used ADO.NET for its data access to SQL Server? That would be a big
win for everyone, no?

[]

To quote from his website (http://www.trigeminal.com/michka.asp,

That's his old website. He's now an MS employee and has a blog.

last para),
"Yet another question I am frequently asked, especially by people
who see me in the newsgroups, is why am I so angry all the time?
The truth is that I am not all that angry. I think that the reason
I am not angry is because I do not leave frustrations bottled up
inside of me, I let them right out. That and my passion for my
work is often mistook as anger. In person, I can be a real
pussycat, sometimes."

Well, I admit I've never met him in person, but if one of the
frequently asked questions about you is "why are you so angry all
the time" (to the point where you feel the need to specifically
address it on your website), well, I don't think there's much more
I need to say.

I *have* met him in person, and he's very pleasant and unassuming,
in fact.

He is very like me in having a very low tolerance for stupidity (or
anything he sees as stupidity). That makes him rather, um, "tart" in
some of his responses. My experience is that he doesn't go for the
jugular by default, but only gets testy when someone has
demonstrated pigheadedness already.

I'm pretty sure I've read it before...it certainly sounds
familiar. And while I may not like him, and I may think ADPs need
another chance (with some admittedly significant
alteration/removal of code under-the-hood), I will whole-heartedly
agree that DAPs were an utter disaster! <bg>

He thought ADPs were a great idea. He just pointed out how badly the
first version had been implemented.

He and I used to clash all the time on the subject of the DOJ case
against MS -- he defended them and I hoped they would lose. He got
pretty exasperated over things I posted many times, but it never
bothered me -- I knew I was *right*.

 

[David W. Fenton]

Actually, what I was suggesting was merging the two (or
conceivably creating a whole new store, but that seems a little
silly) so that you could have a local data store within an ADP
*and* easily have dynamic access to a SQL Server database. If
this theoretical merged model were created, it would even be
backwards compatible with older ADPs with little or no fuss, since
it's simply adding in local storage without changing non-Jet
objects like Forms, Reports, and VBA Modules.

Some people have suggested using XML files for local storage with
ADPs.

I've often wondered if you couldn't use ADO in an ADP to get to Jet
data stored in a local MDB?

 

[Robert Morley]

Some people have suggested using XML files for local storage with
ADPs.

Interesting idea. That would certainly be doable, although XML might have
too much overhead in the end for anything large scale.

I've often wondered if you couldn't use ADO in an ADP to get to Jet
data stored in a local MDB?

If you're doing everything entirely in code, there's no reason you couldn't
store data in a local MDB, though I'm fairly sure you'd have to populate
everything manually. If you really want to confuse a maintenance programmer
(or your replacement, should you ever leave), you could even have your MDB
access in DAO and your SQL Server access in ADO.


Rob

 

[Robert Morley]

The only thing that springs to mind is SEEK, but there's hardly ever
any reason to use that in the first place (as opposed to opening a
recordset with a WHERE clause), and doesn't work except with Jet
data in the first place. So it's not something I've ever used (never
needed it), so it has never been an issue for my upsizing tasks.

I can't think of a single other thing in DAO that was problematic
with linked tables. Can you mention some of them?

Seek was definitely one of them. Having to create fake indices (indexes?)
on tables in some instances was another issue. I'm afraid my DAO is a
little rusty, so other issues have been forgotten at this point.

The only DAO changes I've had to make is adding dbSeeChanges to the
options, and that's pretty darned easy to do with a FIND operation.

What about thing like dynamically constructed queries...changing all the
various double-quotes, single-quotes, square-brackets, etc.? Or does DAO
automatically re-do the syntax when going from your query to the server? I
honestly don't remember at this point.

I don't understand this one. I've hardly ever used filtered
recordsets, except in the odd situation where I open a subset of
records, then want to operate on a subset of that subset. This is
only an issue with recordsets, seems to me, and most editing
operations should be done with SQL statements instead of by
sequentially through a recordset.

While SQL statements are certainly faster, they do tend to become cumbersome
when you want to change data based on complex conditionals (i.e., if FieldX
= 1, then FieldY = 2, but if FieldX = 2 and FieldZ = 3 then FieldY = 3,
etc.) or if you need to check for existence before deciding whether you're
doing an INSERT or UPDATE, that sort of thing.

Being able to re-filter and/or re-sort quickly and easily comes in VERY
handy in cases like these.

In the cases where you need to
sequentially navigate a recordset, one uses a WHERE clause that
returns the smallest possiblen number of records, so I'm having some
difficulty understanding why one would often need to Filter an
existing recordset.

When you want several subsets of the same original at a time, or where you
don't know in advance what your filter will be on an already-open recordset.

Where is this an issue? It's never been one for me.

It's not a common problem, but I've used asynchronous processing on several
occasions so as not to lock a process while editing is occurring.

I think it would have been unwise of MS to invest time and effort in
fixing ADPs to work with classic ADO, which is really obsolete now.
On the other hand, if they'd fixed ADPs to work with ADO.NET and
allowed Access to use ADO.NET in place of classic ADO, that would
have been a big win for everyone.

Yes and no...lots of people still avoid .NET on principle. And if they'd
offered an upgrade path from VBA and/or ADO to .NET that was similar to the
upgrade from VB6 to VB.NET, well, let's just say people STILL complain
vehemently about that problem in the VB6 groups, and we're...what? 8 or 9
years after the initial release of .NET?

Maybe that's in the works?

I've heard hints of all of Office migrating to .NET at some point, but I've
yet to see any solid information on the topic.

What would you say if they made it possible to create an ACCDB that
used ADO.NET for its data access to SQL Server? That would be a big
win for everyone, no?

It could be useful, perhaps. I know there were a lot of complaints about
ADO.NET originally regarding having either server-specific functionality or
slow, bare-bones functionality with the generic version of it. But I admit
I haven't kept up to date, so I don't know if those issues are still a
problem or not.

The advantage of having all SQL objects reflected in the Access GUI,
however, would still be a problem. Sure you can re-link, etc., but you
can't simply hit F5 and have it all there in an instant if you've recently
added several new tables/view/etc.

I *have* met him in person, and he's very pleasant and unassuming,
in fact.

He is very like me in having a very low tolerance for stupidity (or
anything he sees as stupidity). That makes him rather, um, "tart" in
some of his responses. My experience is that he doesn't go for the
jugular by default, but only gets testy when someone has
demonstrated pigheadedness already.

Well, as David has pointed out, I'm not the only one who remembers a
different Michael Kaplan than the one you're talking about. Perhaps he's
had an attitude adjustment over the years after figuring out that a number
of people wanted nothing to do with him.

He thought ADPs were a great idea. He just pointed out how badly the
first version had been implemented.

And I can't particularly argue with him on that, since I STILL think ADPs
are a great idea. MS just seems to be dropping the ball.

He and I used to clash all the time on the subject of the DOJ case
against MS -- he defended them and I hoped they would lose. He got
pretty exasperated over things I posted many times, but it never
bothered me -- I knew I was *right*.

As I'm sure he knew he was right too. It's one of the many benefits of
conviction.


Rob

 

[Jamie Collins]

I think it's a terrible thing to evaluate the case made by someone
on the basis of whether you like them or not. If what they say is
well-argued and fact-based, seems to me that your personal dislike
for them should be irrelevant.

Funny, I thought the reason *you* have a killfile (and like to tell
people about it) is *because* your personal dislike of certain
individuals means you don't want to listen to their well-argued and
fact-based missives.

Jamie.

--

 

[Jamie Collins]

Where is this an issue? It's never been one for me.


I don't understand this one.

This is the problem I have with many people who dismiss ADO: they've
never used it!

You need to use ADO 'in anger' (pun intended) to be able to experience
the required paradigm shift, otherwise you just don't 'get' it.

That's his old website. He's now an MS employee and has a blog.

FWIW he still posts to the 'old' website to this very day.

I *have* met him in person, and he's very <<compliments snipped>>

Another mystery solved: David W. Fenton dismisses ADO solely because
he has more personal experience of Michael Kaplan than ADO itself ;-)

Jamie.

--