ADP Vs. ODBC

[mroks via AccessMonster.com]

Which is the better way to use if i'll going to connect on SQL Server as
backend and MS Access as Frontend.

ADP VS. ODBC. which the two is better?

 

[Sylvain Lafontaine]

Unless you have a lot of knowledge about SQL-Server and ADP, your best
option would be to go with ODBC: much more easier.

 

[mroks via AccessMonster.com]

Thank you for the information.

Unless you have a lot of knowledge about SQL-Server and ADP, your best
option would be to go with ODBC: much more easier.

 

[David W. Fenton]

Which is the better way to use if i'll going to connect on SQL
Server as backend and MS Access as Frontend.

ADP VS. ODBC. which the two is better?

Microsoft is now recommending ODBC.

And is de facto deprecating ADPs.

 

[Robert Morley]

<rant>

Microsoft is now recommending ODBC.

And may the gods help you if you actually want to view and/or access ALL the
objects SQL Server exposes...we're back to the days of manually or
programatically re-linking everything. Oh yay! How does Microsoft NOT see
this as a step backwards?!?

And is de facto deprecating ADPs.

And may the gods help you even further if you've actually got an ADP that
you don't want to convert back to the klunky ODBC way of doing things.

I know there are numerous benefits to linked MDBs such as local caching, and
a few bugs in ADPs...but yet again, this is a case of Microsoft changing
direction (after promoting the ADP/MSDE combo for years), and expecting that
you will happily spend the time and money required to change your business
model to match. UGH!

</rant>


Rob

 

[Mary Chipman [MSFT]]

Microsoft recommends ODBC for *new* applications. Nowhere do we say
anything about recommending that you convert your existing ADP
applications, which will continue to be supported.

The following Access vision whitepaper can be found at:
http://office.microsoft.com/en-us/access/HA102133061033.aspx

This paper has more detail about specific recommendations for using
Access with SQL Server:
http://msdn2.microsoft.com/en-us/library/bb188204.aspx

--Mary

 

[David W. Fenton]

<rant>


And may the gods help you if you actually want to view and/or
access ALL the objects SQL Server exposes...we're back to the days
of manually or programatically re-linking everything. Oh yay!
How does Microsoft NOT see this as a step backwards?!?

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.

You can still use an ADP for design and management, though I prefer
using the SQL Server tools directly.

And may the gods help you even further if you've actually got an
ADP that you don't want to convert back to the klunky ODBC way of
doing things.

Well, I never saw the benefit of ADPs in the first place, to be
honest, so I never used 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. 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.

I know there are numerous benefits to linked MDBs such as local
caching, and a few bugs in ADPs...

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

Microsoft still recommends ADPs as a great reporting platform,
though.

but yet again, this is a case of Microsoft changing
direction (after promoting the ADP/MSDE combo for years), and
expecting that you will happily spend the time and money required
to change your business model to match. UGH!

</rant>

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!

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.

 

[Robert Morley]

Thanks for the links, Mary. I hadn't seen the Access Vision document before.

As far as not supporting ADP goes, correct me if I'm wrong, but I remember
reading reports that you could no longer create ADPs through the Access 2007
interface, only open them. If that's true, that would definitely constitute
a decline of support. Perhaps that was a problem with an early Beta, though.

I'm also confused as to WHY ODBC is recommended. It seems to me that there
are at least as many drawbacks as there are benefits (the lack of ability to
view all SQL Server objects dynamically, the whole mess outlined under
"Adjusting Dynaset Behavior", etc.). I understand that it's faster, but
speed isn't everything (as evidenced by the popularity of the managed .NET
languages).



Rob

 

[Norman Yuan]

Thanks for the links, Mary. I hadn't seen the Access Vision document
before.

As far as not supporting ADP goes, correct me if I'm wrong, but I remember
reading reports that you could no longer create ADPs through the Access
2007 interface, only open them. If that's true, that would definitely
constitute a decline of support. Perhaps that was a problem with an early
Beta, though.

Access2007 can create new ADP project and can also modify SQL Server 2005
objects (i.e. designing tables, views, SPs...) (many ADP developers are
hammered with Access2003 ADP not being able to modify SQL Server2005 server
objects) . However, it took me a bit time to find how to create ADP in
Access2007, MS made it not as obvious as previous Access version, probably
implying the ADP is not recommended.

I'm also confused as to WHY ODBC is recommended. It seems to me that
there

It just because they stopped continuing the investment in ADP, so ODBC is
the only one left. Since ADP is still available in Access so far, thus
"recommended", not because of the technical advantages and disadvantages.

 

[Robert Morley]

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

 

[Robert Morley]

Thanks Norman, I sort of suspected that ODBC might be recommended simply
because of the lack of investment in ADP technology. I remember hearing
something about ODBC being faster (or maybe it was OLE DB? or something
special to do with the ACCDB format?), but I've certainly never had any
major issues with the normal ADP throughput, even over a VPN line.


Rob

 

[Jamie Collins]

by the time I got to the
point where I really needed ADO, it was already obsolete!

In context, ADO is about as 'obsolete' as VBA and (becasue ADO is
build on OLE DB) slightly less 'obsolete' than ODBC. DAO in ACE
(Access2007) should have IMO made ADO in Jet obsolete but DAO still
has significant omissions (row level locking, CHECK constraints, etc)
but let's not do the whole ADO vs DAO thing again.

Jamie.

--

 

[Mary Chipman [MSFT]]

ODBC is recommended because you have more flexibility with the design
of the front-end application, giving you more control over the way
that Access connects to SQL Server and consumes server and network
resources. It doesn't necessarily hang on to a single connection the
way an ADP does. You don't have a design surface for creating SQL
Server objects, but the one supplied with ADPs is incomplete in that
it does not allow you to configure security, among other things. In
addition, it would have been impossible to add support for new
features in SQLS 2005, such as CLR integration. Microsoft recommends
that you use the Developer edition of SQL Server for creating and
securing SQL Server objects because it comes with a complete toolset.

As far as creating an Access front end, it's possible to use a
combinaton of local Jet tables to cache static data for combo boxes,
etc. and to use pass-through queries for reports. Correctly
architected using unbound forms, an Access FE can scale as well as a
..NET application. The optimizing paper gives more details about how to
implement an ODBC-front end successfully.

--Mary

 

[Robert Morley]

ODBC is recommended because you have more flexibility with the design
of the front-end application, giving you more control over the way
that Access connects to SQL Server and consumes server and network
resources. It doesn't necessarily hang on to a single connection the
way an ADP does.

Actually, an ADP will often hang on to more than one connection, so there's
certainly a point there, and I can understand that ODBC gives you greater
flexibility, but am I wrong in thinking that it usually requires a
significantly increased amount of maintenance and tuning compared to an ADP?
The optimization document certainly makes it appear that way, and my
personal experience is in line with that as well.

You don't have a design surface for creating SQL
Server objects, but the one supplied with ADPs is incomplete in that
it does not allow you to configure security, among other things. In
addition, it would have been impossible to add support for new
features in SQLS 2005, such as CLR integration. Microsoft recommends
that you use the Developer edition of SQL Server for creating and
securing SQL Server objects because it comes with a complete toolset.

Which is fine. Let's stipulate for the moment that ADPs are not
well-equipped to design for SQL 2005. After all, you can't do back-end
design work from an ODBC front-end either. So if we remove any design
considerations from the equation, ADP still seems to be a very useful FE
application, particularly in light of the fact that it can catalogue and
interact with SQLS objects far more easily than ODBC will *ever* be able to.

As far as creating an Access front end, it's possible to use a
combinaton of local Jet tables to cache static data for combo boxes,
etc. and to use pass-through queries for reports.

True enough, but how hard would it have been to allow ADP to also create
local tables and views? After all, it already maintains local forms,
reports, etc. It seems to me that with only a little bit of
re-architecting, that should have been a fairly simple procedure. Perhaps
you could even have merged the MDB and ADP concepts into one, with a simple
flick-of-a-switch ability to automatically catalogue all SQLS objects from
one (or more) databases. You'd then have all the power of an MDB with its
local objects, while maintaining the ease-of-use of an ADP.

For the newbie or the advanced user who wants a quick & dirty method of
doing so, you could perhaps have even included a "cache results locally"
flag that could be set for server-side tables and/or static views to grab
them at the next request and automatically cache them locally. You could
even have extended that with a "timeout" property to invalidate the results
and requery the next time they were requested. But instead of actually
investing in the technology you encouraged everybody to use for years, you
decided to drop it and encourage people back to an older and more unwieldy
technology.

Correctly
architected using unbound forms, an Access FE can scale as well as a
..NET application. The optimizing paper gives more details about how to
implement an ODBC-front end successfully.

I've been using Access for some 16 years or so now, and strangely,
optimizing an ODBC front-end hasn't really changed a whole lot in that time.
In fact, I haven't designed an ODBC front-end in about 10 years, and yet
there was absolutely nothing in that document that wasn't a familiar concept
to me. How is going back to the maintenance nightmare of ODBC a step forward?

I realize that you may not be able to address all these points yourself, but
I'm hopeful that since you DO work at Microsoft, you will perhaps bring it
to those who make the decisions that not everyone shares Microsoft's
conviction that ODBC is the right way to go.

While there were definitely a few problems with ADPs, I think with the right
minds and a little work, they could have been a far better front-end
platform than ODBC has the potential to be.


Rob

 

[David W. Fenton]

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]

message news:%[email protected]...

It just because they stopped continuing the investment in ADP, so
ODBC is the only one left. Since ADP is still available in Access
so far, thus "recommended", not because of the technical
advantages and disadvantages.

I think you've got it backwards.

I think they realized after 3 attempts to get ADPs right that there
were major problems with the layer that sits between the ADP and
ADO, and may guess is that it was impossible to fix that without
starting over from scratch.

The Jet layer in an MDB brings its own problems, but the problem
space is well-known and the issues fairly easily resolved.

At least, that's my guess as to why they are abandoning ADPs.

 

[David W. Fenton]

ADP still seems to be a very useful FE
application, particularly in light of the fact that it can
catalogue and interact with SQLS objects far more easily than ODBC
will *ever* be able to.

Can't you do that kind of interaction via OLEDB from your MDB?

 

[David W. Fenton]

[Mary Chipman:]

True enough, but how hard would it have been to allow ADP to also
create local tables and views?

Since ADPs lack Jet, what would your data store be? A local SQL
Server?

 

[David W. Fenton]

I've been using Access for some 16 years or so now, and strangely,
optimizing an ODBC front-end hasn't really changed a whole lot in
that time.

Perhaps that's because ODBC hasn't changed?

 

[Robert Morley]

Re-merging multiple responses....

Can't you do that kind of interaction via OLEDB from your MDB?

The only ways to get catalogues of the objects (if you don't already know
their names, and/or desire dynamic capabilities) are to use the SQL DMO
library, or link directly to system tables and then create the other links
from there...either way, an extra step you need to add.

Perhaps that's because ODBC hasn't changed?

That was pretty much my point...it hasn't gotten any better!

Since ADPs lack Jet, what would your data store be? A local SQL
Server?

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.


Rob