adp vs mdb

[BillE]

I am trying to decide whether it is best to use an ADP or MDB structure with
Access 2007 when upsizing from an existing all-Access mdb to SQL Server.

The more research I do, the more conflicting opinions I find.

For example, I had understood that an MDB will pull an entire table across
the network in order to perform a select operation which retrieves a subset
of records, while an ADP will pull only the selected records across the
network, resulting in a performance improvement. However, I also understand
that Microsoft recommends using MDB instead of ADP with a SQL Server back
end.

How important are security considerations - is an MDB inherently less
secure?

Thanks for your input.

Bill

 

[Robert Morley]

Aaron (the local troll) will be here shortly to tell you ALL about how ADP's
are the only way to go, yadda yadda yadda.

In truth, ADP's probably are the best way to go when you're interfacing with
SQL Server, but Microsoft is currently waffling on whether or not they're
recommended. Considering things like MS's sudden abandonment of the VB6
line, it's hard to say whether you can count on ADP's to still be supported
a few years from now or not.

One of the limitations of ADP's are that they don't support any form of
external data, so if your project needs to pull in data from non-SQL Server
databases or Excel sheets or what have you, then you'll have to code it all
manually. For many, this isn't a problem, but it's definitely something to
keep in mind.

Also, ADP is entirely reliant upon SQL Server to do its work. In an ADP,
you'll have a very hard time doing crosstab tables if you're using SQL
Server 2000 as your back end. SQL Server 2005 supports them, but I'm not
sure what's available in terms of upsizing wizards, or how good they are at
things like crosstab tables. If you go with an MDB format, however, then
Access will "figure it out".

As far as pulling entire tables over, Access is a little smarter than that,
but ultimately, Access will often end up pulling more data over when it's
set up as an MDB than it will as an ADP. The basic idea being that an ADP
will rely on SQL Server to do the work, as previously mentioned, where an
MDB will try to allow SQL Server to do as much of the work as possible, but
will happily pull a million records over the line if it feels it needs to.

There's also the new Access 2007 format to consider...I have absolutely no
experience with it whatsoever, and can't even remember its name off the top
of my head, but I'm sure somebody else will expound upon that for you.



Rob

 

['69 Camaro]

Hi, Bill.

I am trying to decide whether it is best to use an ADP or MDB structure
with Access 2007 when upsizing from an existing all-Access mdb to SQL
Server.

Hold off on the ADP if you're using Access 2007. There are some serious
performance issues with ADP's that may be addressed in the first service
pack. If you really want to use an ADP and you have to deploy it now,
consider using an Access 2003 ADP instead.

For example, I had understood that an MDB will pull an entire table across
the network in order to perform a select operation which retrieves a
subset of records

This happens when an inexperienced or forgetful Access database developer
doesn't put an index on the table. Only the necessary indexed records will
be returned across the network, so without an index, all records in the
table get transferred across the network.

How important are security considerations - is an MDB inherently less
secure?

An MDB is far less secure, because it's a file-based database, so a hacker
only needs network access to the database file itself to eventually discover
all the secrets inside the file. If you require security, store the data in
a client/server database, such as SQL Server or Oracle. It doesn't matter
if the front end is an MDB or ADP if the back end is SQL Server, but if the
front end is an ADP, then the only back end possible is in the SQL Server
family (i.e., SQL Server, SQL Server 2005 Express Edition, or MSDE). If
you're going to be upgrading the database server to Vista or Windows Server
2008 in the future, ensure that you're using SQL Server 2005 or SQL Server
2005 Express Edition, because Vista and Windows Server 2008 don't support
SQL Server 2000 or MSDE. The front end hosts can be on any OS that supports
that version of Access (i.e., Windows XP SP-2, Windows 2003 Server SP-1, or
Vista for Access 2007; Windows 2000 SP-3, Windows XP, Windows 2003 Server,
or Vista for Access 2003; Windows 95 and newer for older versions of
Access).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Sylvain Lafontaine]

There are many conflicting opinions because MS is probably (as this is
unofficial, not official) in the process of replacing ADP with the .NET
Framework (obviously, they don't want to support more than two programming
models) and many people want to see this as the *proof* that using ODBC
linked tables is superior to using ADP.

Personally, I don't see (or have) any need to have any kind of proof and
indeed, if this was such a proof then you would see a lot of linked tables
in .NET. (Don't bother to search, you will find none.). Maybe these people
are thinking that if they can prove that ODBC linked tables are superior to
ADP, then they would also have proven that they are also superior to .NET; I
don't know but again, I don't see any reason why someone would want to enter
such a discussion. Maybe it's because they have some kind of personal
interest or they have made some promise to their clients and they want to
prove it to them and to everyone else; I don't know.

In your case, start with the beginning and upgrade to a MDB file with linked
table. By peppering your code with the dbSeeChanges option everywhere that
Access will tell you, you should be done in less than a few hours. After
that, if you have a performance problem, then it will be time for you to
look for one or more alternate solutions (using Views, create your own
recordsets, use unbound forms and sql pass-through, go with ADP or, if you
want a long term solution, go with .NET).

Another thing that you might want to look after would be the need for strong
security. In my opinion, only .NET is offering any real security at this
moment and everything else will only give you a false sentiment of security.
However, if you don't really need a strong security, then this would be
overkill if you were to make your choice based only on this fact.

As for ADP 2007, there have been many reports of performance problems with
ADP 2007 in comparaison of ADP 2003 but I cannot tell you if you will be hit
with the same kind of problem or if it will be corrected in the next service
pack.

 

[David W. Fenton]

Hold off on the ADP if you're using Access 2007. There are some
serious performance issues with ADP's that may be addressed in the
first service pack. If you really want to use an ADP and you have
to deploy it now, consider using an Access 2003 ADP instead.

Microsoft now recommends MDBs over ADPs, no matter the version of
Access. I'd take them at their word and use an MDB in all cases.

ADPs are clearly going out the window, so it's a dead-end
development platform, one that I don't believe ever had any
justification for existing in the first place.

This happens when an inexperienced or forgetful Access database
developer doesn't put an index on the table. Only the necessary
indexed records will be returned across the network, so without an
index, all records in the table get transferred across the
network.


An MDB is far less secure,

This seems to me to be confusing issues. If you're choosing a front
end, MDB or ADP (ADPs don't store data, of course), then the back
end is SQL Server, or you wouldn't be able to use an ADP. Security
is then EXACTLY THE SAME between the two.

Security is only different when you're comparing a Jet back end
(MDB) to a SQL Server back end. In that case, ADP would not be an
option in any event.

because it's a file-based database, so a hacker
only needs network access to the database file itself to
eventually discover all the secrets inside the file. If you
require security, store the data in a client/server database, such
as SQL Server or Oracle.

If he's considering an ADP, then OBVIOUSLY the back end is already
in SQL Server, no?

It doesn't matter
if the front end is an MDB or ADP if the back end is SQL Server,
but if the front end is an ADP, then the only back end possible is
in the SQL Server family (i.e., SQL Server, SQL Server 2005
Express Edition, or MSDE).

The question in the subject heading of this thread only comes up if
the back end is SQL Server, so there is NO security difference
between MDB and ADP front ends.

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

There are many conflicting opinions because MS is probably (as
this is unofficial, not official) in the process of replacing ADP
with the .NET Framework (obviously, they don't want to support
more than two programming models) and many people want to see this
as the *proof* that using ODBC linked tables is superior to using
ADP.

Tne .NET framework is simply not relevant to Access development. The
only options for Access development are MDB and ADP, and Microsoft
has been recommending MDB over ADP for a couple of years now.

The reason MS gives in their documentation is that ADPs have as many
or more layers between your front end and the database as MDBs do,
and those layers are no better at the job than Jet.

Personally, I don't see (or have) any need to have any kind of
proof and indeed, if this was such a proof then you would see a
lot of linked tables in .NET. (Don't bother to search, you will
find none.). Maybe these people are thinking that if they can
prove that ODBC linked tables are superior to ADP, then they would
also have proven that they are also superior to .NET;

I don't understand why in the context of Access development you even
mention .NET -- it's not relevant.

In the context of Access development, Microsoft has determined that
MDBs with ODBC perform better than ADPs, and they've changed their
recommendations to Access developers developers working with SQL
Server accordingly.

I
don't know but again, I don't see any reason why someone would
want to enter such a discussion. Maybe it's because they have
some kind of personal interest or they have made some promise to
their clients and they want to prove it to them and to everyone
else; I don't know.

I, personally, have not seen any such discussion. An existing MDB
app is not going to benefit in any way whatsoever by being converted
to ADP, and it's going to be a huge amount of work. If you're
looking at new development, the legacy app is not involved, so it's
a more equal comparison, but MS itself is now saying that MDBs are
better than ADPs. Given that this is a 180 degree reversal of their
position on the subject, it seems like a pretty good justification
for going with MDBs instead of ADPs, which were never a very mature
or stable platform (granted, they've only existed for 4 versions of
Access, in comparison to the MDB with has been the basis of Access
from the beginning).

In your case, start with the beginning and upgrade to a MDB file
with linked table. By peppering your code with the dbSeeChanges
option everywhere that Access will tell you, you should be done in
less than a few hours. After that, if you have a performance
problem, then it will be time for you to look for one or more
alternate solutions (using Views, create your own recordsets, use
unbound forms and sql pass-through, go with ADP or, if you want a
long term solution, go with .NET).

I can't see converting any existing MDB to an ADP because it will
mean completely re-engineering an app that's already working, and
that's bound to cause enormous problems (cf. Netscape/Mozilla). And
..NET is simply irrelevant to Access -- it's not a RAD solution in
comparison to either MDB or ADP.

Your recommendations for MDB are solid, though -- start with the
minimal conversion and then do more to fix the areas that don't
perform well.

Another thing that you might want to look after would be the need
for strong security. In my opinion, only .NET is offering any
real security at this moment and everything else will only give
you a false sentiment of security.

Security of *what*? Data security is an issue for the database
engine, and since the comparison is between MDB and ADP, SQL Server
is the only database engine, and that offers security capabilities
as good as you can find in any database engine anywhere.

If you're talking about security of the front end project, I don't
see an issue, either, since you can distribute either an MDE or ADE,
which has no code.

However, if you don't really need a strong security, then this
would be overkill if you were to make your choice based only on
this fact.

Where is security an issue at all in comparing MDB and ADP? .NET
can't get you any more security in the back end data store than is
already provided.

 

[BillE]

Thanks for the response - the consensus is evident, although I didn't get a
response from a troll.

I'm trying to find citations from Microsoft regarding this - do they stand
up and spit it out, or is it obfuscated?

-Bill

 

[Sylvain Lafontaine]

ADP are mainly based on the use of stored procedures (SP). If your database
is not complexe or big enough to require the use of multi-steps SP or of
query statements too complexe for JET (and as you are converting an existing
MDB database, chances are very high that this is exactly your case) then you
should begin with a MDB file and ODBC linked tables as your first step.

Later, when you will have deeper knowledge of SQL-Server and of SP, you will
have the possibility to choose a more advanced solution if you have the need
to do so.

 

[Mary Chipman [MSFT]]

Here's the official word, not from a troll

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn2.microsoft.com/en-us/library/bb188204.aspx

and the position paper:
http://office.microsoft.com/en-us/access/HA102133061033.aspx

-Mary

 

[Robert Morley]

Tne .NET framework is simply not relevant to Access development. The

only options for Access development are MDB and ADP, and Microsoft
has been recommending MDB over ADP for a couple of years now.

I think his point was that you might want to consider .NET as the front end
instead of Access, especially if you're doing Web/Intranet development. I
don't find .NET useful for much, personally, but it has its advantages in
some respects, so others may find it more useful for whatever they're doing.

The reason MS gives in their documentation is that ADPs have as many
or more layers between your front end and the database as MDBs do,
and those layers are no better at the job than Jet.

I've never seen a strong proof of this. Can you point me to something? I'm
not necessarily disagreeing, but I'd be curious to see more in-depth
analyses than I've seen out of MS to-date.

In the context of Access development, Microsoft has determined that
MDBs with ODBC perform better than ADPs, and they've changed their
recommendations to Access developers developers working with SQL
Server accordingly.

Don't forget, MS has a vested interest in this. They also claimed that
VB.NET was significantly faster than VB6 was, and it was true in a few
highly limited cases. But for most real-world tests, VB.NET proved to be
slower...sometimes quite significantly. MS has a "latest and greatest"
approach to things that doesn't always jive with the real world, so it's not
a good idea to simply take their word for it. Often, they're right, but
often they're trying to get you to buy their newest product, and skewing
things in that product's favour.

MS itself is now saying that MDBs are
better than ADPs. Given that this is a 180 degree reversal of their
position on the subject, it seems like a pretty good justification
for going with MDBs instead of ADPs

Actually, if anything, that makes me highly suspicious. While highlighting
the "latest and greatest" approach I mentioned before, the fact that they
would universally encourage everybody towards ADPs, then unequivocally urge
them back towards MDBs gives me serious cause to distrust their statements.
I'm not particularly anti-MS, but lately, it seems that a reversal of
position is common for them, and that's not a good sign.



Rob

 

['69 Camaro]

Hi, David.

Microsoft now recommends MDBs over ADPs, no matter the version of
Access. I'd take them at their word and use an MDB in all cases.

ADP's have a few advantages over MDB's. That Bill is even considering ADP's
means those advantages are important to him (or, more likely, his
organization), so I'm not likely to tell him there's never a good reason to
use ADP's.

ADPs are clearly going out the window, so it's a dead-end
development platform

The same can be said for VB6, but it has legions of fans and development
continues years after Visual Basic .Net was unleashed in the development
world. I came from the Unix and VAX world, where development still goes on
on obsolete operating systems, equipment, and development platforms, because
"that's what we have to work with," not because the vendor decided to make
other products and no longer support older products. Just because the
vendor decides it's a dead-end product doesn't mean every one of the
customers does, too.

This seems to me to be confusing issues. If you're choosing a front
end, MDB or ADP (ADPs don't store data, of course), then the back
end is SQL Server, or you wouldn't be able to use an ADP. Security
is then EXACTLY THE SAME between the two.

Since Bill commented about the side effects of an MDB data source and he
currently has an MDB back end, I can't tell when reading his question
whether or not he specifically wanted to know about security on the front
end MDB file or on MDB files in general. Therefore, I gave the generic
answer regarding security on an MDB file.

If he's considering an ADP, then OBVIOUSLY the back end is already
in SQL Server, no?

No. It's currently an MDB. He mentioned "when upsizing from an existing
all-Access mdb to SQL Server" in his first post. He hasn't upsized yet, but
is deciding on a plan of action and wants advice on which development path
to take on the front end. Otherwise, he would have stated that the back end
was in SQL Server and never mentioned "an existing" mdb.

The question in the subject heading of this thread only comes up if
the back end is SQL Server, so there is NO security difference
between MDB and ADP front ends.

He switched gears within the thread when he commented about an MDB pulling
all records in a table across the network, so I answered his question in the
context of security on MDB's in general, because the data is going to be set
up (hopefully) securely in the SQL Server back end, which wouldn't make a
difference whether or not the front end was an MDB or ADP.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[David W. Fenton]

ADP's have a few advantages over MDB's. That Bill is even
considering ADP's means those advantages are important to him (or,
more likely, his organization), so I'm not likely to tell him
there's never a good reason to use ADP's.

Well, *maybe* it means that. Many people choose ADPs for stupid
reasons, like the fact that it doesn't use Jet.

The same can be said for VB6, but it has legions of fans and
development continues years after Visual Basic .Net was unleashed
in the development world. I came from the Unix and VAX world,
where development still goes on on obsolete operating systems,
equipment, and development platforms, because "that's what we have
to work with," not because the vendor decided to make other
products and no longer support older products. Just because the
vendor decides it's a dead-end product doesn't mean every one of
the customers does, too.

But VB6 is a mature development platform with a huge base of users
and a vast array of sources of help and code.

ADPs are very new and never reached maturity. Once MS stops
developing them (if they haven't already), then there really won't
be any reason to keep using them, as there is neither the same kind
of developer community nor the same level of maturity and stability
that VB6 has as a development platform.

Microsoft never finished ADPs, in my opinion. Michael Kaplan's
telling criticisms of ADPs as implemented in Access 2000 were never
really fully addressed by Microsoft.

Since Bill commented about the side effects of an MDB data source
and he currently has an MDB back end, I can't tell when reading
his question whether or not he specifically wanted to know about
security on the front end MDB file or on MDB files in general.
Therefore, I gave the generic answer regarding security on an MDB
file.

But that security question doesn't have anything to do with the
choice of MDB or ADP, though, since ADP is not a choice for his MDB
back end.

No. It's currently an MDB.

But "currently" doesn't matter as the question is about the
*future*, or ADP wouldn't be under consideration at all.

He mentioned "when upsizing from an existing
all-Access mdb to SQL Server" in his first post. He hasn't
upsized yet, but is deciding on a plan of action and wants advice
on which development path to take on the front end. Otherwise, he
would have stated that the back end was in SQL Server and never
mentioned "an existing" mdb.


He switched gears within the thread when he commented about an MDB
pulling all records in a table across the network, so I answered
his question in the context of security on MDB's in general,
because the data is going to be set up (hopefully) securely in the
SQL Server back end, which wouldn't make a difference whether or
not the front end was an MDB or ADP.

I think your answer was *very* confusing, as it did not make it
clear that you were addressing a different question than that of the
thread itself.

 

[David W. Fenton]

I think his point was that you might want to consider .NET as the
front end instead of Access, especially if you're doing
Web/Intranet development. I don't find .NET useful for much,
personally, but it has its advantages in some respects, so others
may find it more useful for whatever they're doing.

But .NET is not a RAD platform. And I don't see that it's even in
the same ballpark as either MDB or ADP in terms of ease of use and
deployment.

In short, I don't think it *should* be considered alongside the two
at all -- it makes no sense.

I've never seen a strong proof of this. Can you point me to
something? I'm not necessarily disagreeing, but I'd be curious to
see more in-depth analyses than I've seen out of MS to-date.

From Microsoft's own documentation for Access:

http://technet2.microsoft.com/Office/en-us/library/1dce641e-ba1c-446a
-8ff2-221769a58ba51033.mspx?mfr=true

Access Data Projects (ADPs)

An Access Data Project is an OLE document file, like the .xls
or.doc file formats. It contains forms, reports, macros, VBA
modules, and a connection string. All tables and queries are
stored in SQL Server. The ADP architecture was designed to create
client-server applications. Because of this, there is a limit to
the number of records that Access returns in any recordset. This
limit is configurable, but you typically must build enough
filtering into your application so that you do not reach the
limit.

Access uses OLEDB to communicate with SQL Server. To provide the
Jet-like cursor behavior desired for desktop applications, Access
implements the Client Data Manager (CDM) as an additional layer
between Access and OLEDB.

Because of the layers required to get from Access to SQL Server in
the ADP architecture, it is often easier to optimize MDB/ACCDB
file solutions. However, there are some scenarios where a report
might be generated significantly faster in an ADP file. To add
these performance improvements and retain the flexibility of SQL
Server, you can build the majority of the application in an MDB or
ACCDB file and have the file load reports from a referenced ADP
file.

One advantage that ADP files have over files in MDB or ACCDB
format is the ability to make design changes to SQL Server
objects. ADP files include graphical designers for tables, views,
stored procedures, functions, and database diagrams.

Don't forget, MS has a vested interest in this.

Not sure what that interest would be. Many of the things that MS
touted in the release of Access 2000 that were for Enterprise
developers have since been abandoned in favor of the previous
technologies. That would include ADO being abandoned in favor of DAO
for Jet, and ADPs being deprecated for SQL Server in favor of
MDBs/ODBC. It was obvious to me at the time A2K was released that
the ADO for Jet recommendation was bloody stupid, and by A2K3 MS had
recognized that fact.

I also was very suspicious of ADPs, as it seemed their whole
justification for existence was simply to avoid Jet, which makes no
sense to me.

They also claimed that
VB.NET was significantly faster than VB6 was, and it was true in a
few highly limited cases. But for most real-world tests, VB.NET
proved to be slower...sometimes quite significantly.

This is the opposite situation in comparison to the ADP deprecation.
VB.NET is the NEW platform and MS is promoting it, just as when ADP
was the new platform, MS promited *it*. Now, they have abandoned
what was once new (ADP) in favor of the old (MDB/ODBC).

When MS reverses themselves and admits that VB6 is faster than
VB.NET in a lot of cases, then that will be *very* believable,
because it will contradict the previous "latest and greatest"
policy, just as with ADO/DAO for Jet and ADP vs. MDB.

MS has a "latest and greatest"
approach to things that doesn't always jive with the real world,
so it's not a good idea to simply take their word for it. Often,
they're right, but often they're trying to get you to buy their
newest product, and skewing things in that product's favour.

But in the case of deprecating ADPs, MS is going *against* the
"latest and greatest" bias. That alone makes it seem very credible
to me, as it contradicts a policy that was implemented in 1999.

Actually, if anything, that makes me highly suspicious. While
highlighting the "latest and greatest" approach I mentioned
before, the fact that they would universally encourage everybody
towards ADPs, then unequivocally urge them back towards MDBs gives
me serious cause to distrust their statements. I'm not
particularly anti-MS, but lately, it seems that a reversal of
position is common for them, and that's not a good sign.

They promoted ADPs and it didn't work out well, so now they are
promoting the older platform, the one that we already knew worked
just fine. They had to eat crow on this one (just as they did with
ADO/DAO for Jet).

And, frankly, the emperor had no clothes in 1999. Michael Kaplan saw
that and wrote an article about it. And I could clearly see that the
ADO for Jet push was completely wrong-headed. I was always suspcious
of the "They don't use JET!!!!!" justification for ADPs, because I
don't see Jet as a bad thing. But many ill-informed people blamed
Jet for their inability to build stable Access applications.

In the case of the current deprecation of ADPs, it makes perfect
sense to me, as all the problems that were adduced by experts way
back in 1999 have become obvious enough to MS that they decided it's
not worth trying to correct.

 

[David W. Fenton]

Here's the official word, not from a troll

Optimizing Microsoft Office Access Applications Linked to SQL
Server http://msdn2.microsoft.com/en-us/library/bb188204.aspx

and the position paper:
http://office.microsoft.com/en-us/access/HA102133061033.aspx

I don't see any really definite statements in there that are as
strong as the ones in the article I cited:

http://technet2.microsoft.com/Office/en-us/library/1dce641e-ba1c-446a
-8ff2-221769a58ba51033.mspx?mfr=true

 

[Robert Morley]

ADPs are very new and never reached maturity. Once MS stops

developing them (if they haven't already), then there really won't
be any reason to keep using them, as there is neither the same kind
of developer community nor the same level of maturity and stability
that VB6 has as a development platform.

I'm not sure I can agree with that. There is no facility in MDB format to
automatically have complete access to ALL of the tables, views, SPs, etc.,
exposed by any given SQL Server database. You have to come up with some
other linking method, and either know before-hand what it is you want to
link to, or jump through no end of hoops to retrieve listings of all the
various objects.

Microsoft never finished ADPs, in my opinion. Michael Kaplan's
telling criticisms of ADPs as implemented in Access 2000 were never
really fully addressed by Microsoft.

I'm not sure I'd trust Michael Kaplan's criticisms entirely. He's certainly
technically knowledgeable, but he can be more than a little acerbic with his
comments, which may lead to an unfairly biased opinion of an otherwise minor
problem. It could be that he's entirely correct, and possibly has
highlighted major problems, but I wouldn't quote him as your only source,
unless you know for certain that the problems he's highlighting are
significant to most ADP users.



Rob

 

[Robert Morley]

But .NET is not a RAD platform. And I don't see that it's even in

the same ballpark as either MDB or ADP in terms of ease of use and
deployment.

In short, I don't think it *should* be considered alongside the two
at all -- it makes no sense.

As a general rule, I agree, but if you're publishing to the Web, I don't
think you should be considering Access as a front end, either. Not sure if
..NET is the way to go or not in that instance, but it probably deserves
mention, at least.

From Microsoft's own documentation for Access:

http://technet2.microsoft.com/Offic...ba1c-446a-8ff2-221769a58ba51033.mspx?mfr=true

Actually, it says nothing about the number of layers. It implies it, but it
could just as easily be indicating that there's an inherent design
difference in those layers which makes it more or less efficient in certain
situations. In fact, they even tell you straight out that there are
situations in which an ADP would be preferable, though they're very careful
not to elaborate on those too much, cuz they want you to use their latest &
greatest. Like I said, I'd like to see STRONG proof of it...something that
involves timing tests in real-world situations.

True, you can optimize *some* SQL Server projects using local tables and so
forth, but if your project doesn't lend itself to those types of
optimizations, the reasons to use MDB/ACCDB go down rather significantly.
All in all, they've made the two formats seem about equal to my mind, and
that you should consider which one you use for any given project based on
the needs of that project.

Not sure what that interest would be.

You've bought their older software, now they're trying to convince you that
with the new software, with the CDM, it performs better, and that you should
buy it. If that's not a vested interest, I don't know what is. I've yet to
see tangible proof of better performance from MS, just vague statements that
rely on changing your approach, like the one you referred to earlier. (FYI,
if you really want to switch to that approach, you can cache tables in ADP
as well, though it takes more work and you have to use ADO to store the
tables in some other format locally or in memory if it's small enough.)

That would include ADO being abandoned in favor of DAO
for Jet

DAO *is* definitely a much better solution for Jet, but I haven't seen a lot
of Jet apps in Enterprise solutions...especially not those having large
numbers of simultaneous users. ADO has a lot of advantages when it comes to
things like disconnected recordsets, asynchronous processing, etc. I've
seen far more ADO in recent years than DAO, so I'm not sure I'd say it's
abandoned.

and ADPs being deprecated for SQL Server in favor of
MDBs/ODBC.

Here again, this is mostly in MS's mind. I know lots of people who develop
in ADP...in fact, just looking around this newgroup will pretty much prove
that point.

It was obvious to me at the time A2K was released that
the ADO for Jet recommendation was bloody stupid
Agreed.

I also was very suspicious of ADPs, as it seemed their whole
justification for existence was simply to avoid Jet, which makes no
sense to me.

That's now how I see them at all. Their primary purpose, as I see it, is to
allow you to interface with SQL Server and treat it almost as though it were
a local Access database, complete with the full object list, the ability to
make design changes, and even add things like lookup columns that SQL Server
doesn't support natively. MS *did* push a little too much, mind you, and I
think you should only use ADP's where it makes sense to do so. I've seen
some who recommend an ADP/SQL Server configuration for even something like a
small, single-table database, which is just dumb in my opinion.

This is the opposite situation in comparison to the ADP deprecation.
VB.NET is the NEW platform and MS is promoting it, just as when ADP
was the new platform, MS promited *it*. Now, they have abandoned
what was once new (ADP) in favor of the old (MDB/ODBC).

But in either case, you have to stop and ask yourself *why* they're
promoting it. Is it because it's better for the user, or better for them?
By law, in many cases, a company's first priority MUST be to its
stockholders, so it may not always be the end-user they're thinking of when
they make recommendations.

In any event, I'm not particularly recommending ADP or MDB or ACCDB; I'm
recommending that the person ask themselves what it is they want to
accomplish and decide from there. They *all* have benefits and drawbacks.

But in the case of deprecating ADPs, MS is going *against* the
"latest and greatest" bias. That alone makes it seem very credible
to me, as it contradicts a policy that was implemented in 1999.

Again, I want proof, not just MS's word for it. And even it it IS faster,
do the features you get out of ADP make more sense for your particular
project, or is an MDB or ACCDB more appropriate. That's for each user to
decide on a per-project basis...I don't believe in saying that one is
inherently better than the other unless it is in all cases.

And, frankly, the emperor had no clothes in 1999. Michael Kaplan saw
that and wrote an article about it. And I could clearly see that the
ADO for Jet push was completely wrong-headed. I was always suspcious
of the "They don't use JET!!!!!" justification for ADPs, because I
don't see Jet as a bad thing. But many ill-informed people blamed
Jet for their inability to build stable Access applications.

Agreed. Jet was never a bad thing in my mind, nor was DAO. ADO has its
advantages in some situations (as previously mentioned), but with native
Access/Jet data, there's only a very few situations in which ADO is
advantageous. DAO is almost always the better choice. By the same token,
however, I think that either MDB's, ACCDB's, or ADP's can be great
front-ends to a SQL Server project, provided that you're aware of the
benefits and limitations of each. If you want to be able to make design
changes without loading up SQL EM or similar, then MDB's and ACCDB's just
aren't going to do it for you.

In the case of the current deprecation of ADPs, it makes perfect
sense to me, as all the problems that were adduced by experts way
back in 1999 have become obvious enough to MS that they decided it's
not worth trying to correct.

Actually, the "deprecation" has only been hinted at in a blog, AFAIK, it
hasn't been announced officially. I think they're still dithering on that
one because of the inherent trade-offs between the two approaches.



Rob

 

[David W. Fenton]

I'm not sure I can agree with that. There is no facility in MDB
format to automatically have complete access to ALL of the tables,
views, SPs, etc., exposed by any given SQL Server database. You
have to come up with some other linking method, and either know
before-hand what it is you want to link to, or jump through no end
of hoops to retrieve listings of all the various objects.

How is this a problem? If you have SQL Server, you surely have the
management tools that allow you to control and administer the
database. While it was convenient to have access to those functions
in an ADP, it was still not as convenient or as full-featured as the
native SQL Server administrative/management tools.

I'm not sure I'd trust Michael Kaplan's criticisms entirely. He's
certainly technically knowledgeable, but he can be more than a
little acerbic with his comments, which may lead to an unfairly
biased opinion of an otherwise minor problem.

Did you read the article? If not, then you have no basis for
commenting on it.

And I just don't see how comments being "acerbic" can change their
factual status.

It could be that he's entirely correct, and possibly has
highlighted major problems, but I wouldn't quote him as your only
source, unless you know for certain that the problems he's
highlighting are significant to most ADP users.

I'd trust Michael Kaplan over just about anyone you could name.
Maybe you don't know about his unequalled intelligence and
integrity, but *I* certainly do.

And his criticisms were entirely sensible.

And by publishing those criticisms, he greatly offended the Access
team and for many months was persona non grata there. Given that
Michael was employed by the Access team in a number of areas, that
was a pretty significant thing to have happened. To me, it just
emphasizes his integrity, that he told the truth as he saw it even
when it could cost him lucrative consulting contracts.

 

[Robert Morley]

How is this a problem? If you have SQL Server, you surely have the

management tools that allow you to control and administer the
database. While it was convenient to have access to those functions
in an ADP, it was still not as convenient or as full-featured as the
native SQL Server administrative/management tools.

Well, how about the case where you've got generic functions enumerating
items in your database. For example, in my database at work, I have an
"Export Verbatims" form that enumerates all the views starting with the word
"Verbatims" (which are guaranteed to all include the same columns, etc.),
and then exports whichever one the user chooses. These are frequent ad hoc
requests that we get, and all I have to do is create a new one to satisfy
the conditions required, then tell the user that it's available. I don't
have to add data to any tables, I don't have to re-release anything...they
just open a form and export. Nice and simple.

Did you read the article? If not, then you have no basis for
commenting on it.

Well, you never posted a link to the article that I saw, so how was anybody
to read it or comment on it? Besides which, I wasn't commenting on the
article itself, I was commenting on my (and others') experience with
Michael.

And I just don't see how comments being "acerbic" can change their
factual status.

They change their impact on the reader. By making a travesty out of minor
issues, an author can make it appear that there are major problems with the
product when there aren't (aka, "blowing it out of all proportion"). I have
absolutely no idea if Michael did so in that article or not, I'm just saying
that in ANY case, you shouldn't just take the word of one author, and my
personal experience with him has lead me to be very wary of anything from
him that involves an opinion. He certainly seems to have a lot of them.

I'd trust Michael Kaplan over just about anyone you could name.
Maybe you don't know about his unequalled intelligence and
integrity, but *I* certainly do.

Oh, I'm well aware of his intelligence, and I have nothing but good things
to say about his programs. His arrogance, however, is also unequalled, and
it's that which makes me take a step back and look at anything he writes
with a certain degree of critical thinking.

And his criticisms were entirely sensible.

As I said, they may well have been. Without a link to the article, I'm not
in a position to comment.

And by publishing those criticisms, he greatly offended the Access
team and for many months was persona non grata there.

As I understood it, it was his arrogant "my way or the highway" attitude
that made him persona non grata there, and I believe that happened well in
advance of ADP.

Given that
Michael was employed by the Access team in a number of areas, that
was a pretty significant thing to have happened. To me, it just
emphasizes his integrity, that he told the truth as he saw it even
when it could cost him lucrative consulting contracts.

And therein lies my point...he told the truth as he saw it. How he sees
things, however, is almost invariably tainted by his opinion of his own
greatness.

So once again, his article may well have been completely legitimate, but I'd
want to hear others' opinions of it, and of course read it for myself,
before blindly taking his word that there's a problem with ADP's. Certainly
in the times I've used them, I've had no significant issues with them.



Rob

 

[David W. Fenton]

[]

Well, you never posted a link to the article that I saw, so how
was anybody to read it or comment on it? Besides which, I wasn't
commenting on the article itself, I was commenting on my (and
others') experience with Michael.

So far as I know, the article was never posted online. I read it in
the printed magazine.

They change their impact on the reader.

Big deal. Are you so overly emotional that you can't read and
article and evaluate it on its factual content?

By making a travesty out of minor
issues,

This is not something that Michael Kaplan has ever been guilty of
doing.

an author can make it appear that there are major problems with
the product when there aren't (aka, "blowing it out of all
proportion"). I have absolutely no idea if Michael did so in that
article or not, I'm just saying that in ANY case, you shouldn't
just take the word of one author,

The logic of his criticisms were quite clear.

and my
personal experience with him has lead me to be very wary of
anything from him that involves an opinion. He certainly seems to
have a lot of them.

My experience is that all of his opinions bear careful
consideration, as they are based on deep knowledge of the subjects
in question.

The only place I've ever disagree with him is on the value of Robert
Henlein's oeuvre!

Oh, I'm well aware of his intelligence, and I have nothing but
good things to say about his programs. His arrogance, however, is
also unequalled, and it's that which makes me take a step back and
look at anything he writes with a certain degree of critical
thinking.

I'd say his arrogance is justified. The kind of abuse he took for
years on Usenet for simply telling the truth always amazed me.
People very frequently attacked him as the bearer of the bad news,
as though they didn't want to face up to reality.

[]

As I understood it, it was his arrogant "my way or the highway"
attitude that made him persona non grata there, and I believe that
happened well in advance of ADP.

This situation was quite different. Michael wrote the replication
conflict wizard that shipped with A2K, so he was obviously on good
terms with the Access team throughout the A2K development process.
It was after the publication of his "emperor has no clothes" article
that MS cut him off. That didn't last too long (he is now an MS
employee, of course), but it was a major problem for him for a
while.

And therein lies my point...he told the truth as he saw it. How
he sees things, however, is almost invariably tainted by his
opinion of his own greatness.

I completely disagree on that. Michael has always been forceful in
the expression of his opinions, but never at the expense of truth.

So once again, his article may well have been completely
legitimate, but I'd want to hear others' opinions of it, and of
course read it for myself, before blindly taking his word that
there's a problem with ADP's. Certainly in the times I've used
them, I've had no significant issues with them.

Well, you're very lucky. I know any number of very experienced
Access developers who desperately wanted ADPs to work, but
eventually, after years of struggle, completely gave up on them.

 

[Robert Morley]

Big deal. Are you so overly emotional that you can't read and

article and evaluate it on its factual content?

Actually, that's what I'm suggesting that people make sure they do when
reading one of Michael's articles. *Make sure* you evaluate it's factual
content, and don't get lost in Michael's wording. While we're on the
subject, do you remember any of the flaws or issues the article pointed out?
ADP's were a fledgling technology in 2000, so it's possible that the
problems were addressed in later versions.

This is not something that Michael Kaplan has ever been guilty of
doing.

Oh, and you know this how? On his own website, he actually has (had?) a
response to people who found him to be overly negative or emotional.
Whether you agree or disagree that he is, obviously it's something that
others besides me have accused him of.

My experience is that all of his opinions bear careful
consideration, as they are based on deep knowledge of the subjects
in question.

Quite true. But I have personally seen him rant at least as much as anybody
else, if not more, and whether it's based in fact or not, a rant is still a
rant, and may make something out to be more of a problem than it is.

I'd say his arrogance is justified. The kind of abuse he took for
years on Usenet for simply telling the truth always amazed me.
People very frequently attacked him as the bearer of the bad news,
as though they didn't want to face up to reality.

That might be an excuse for him to be pissed, but that's certainly no excuse
for arrogance. In fact, I can't really think of an excuse for someone to be
arrogant. Besides, if you can't take a little heat, then you don't belong
in newsgroups.

This situation was quite different. Michael wrote the replication
conflict wizard that shipped with A2K, so he was obviously on good
terms with the Access team throughout the A2K development process.
It was after the publication of his "emperor has no clothes" article
that MS cut him off. That didn't last too long (he is now an MS
employee, of course), but it was a major problem for him for a
while.

I wasn't aware that he was back at MS now, but then, I don't exactly try to
keep track of his life. As for the details of why MS gave him the boot,
only he and MS know that for sure, but my impression from comments I heard
around that time was that there were personality conflicts.

I completely disagree on that. Michael has always been forceful in
the expression of his opinions, but never at the expense of truth.

That's not what I was saying. What I was saying was that his opinions are
tainted by his perceptions (as are all our opinions). For example, many
people believe VB.Net is great, many people believe it's inherently flawed.
In fact, both are right, it simply depends on where they're coming from and
what they need out of the language. But getting back to Michael, his
opinions of the relative importance of some issues often don't match many
other peoples', for right or for wrong. There's no doubt in my mind,
however, that if he brings up a point, it's something to at least be looked
at...just don't think that because he says a product has flaws that it's a
big deal. All products have flaws, the question is whether it's really an
issue for any given person.

Well, you're very lucky. I know any number of very experienced
Access developers who desperately wanted ADPs to work, but
eventually, after years of struggle, completely gave up on them.

I'd love to hear from those people, because never mind that I've never had
problems, I've never heard of others who have (within the limits of the
technology, mind you...if someone wants to cache something in a local table,
that's a simple limitation of the technology, not a problem per se).

If the product were as flawed as you seem to suggest, I suspect we would
have heard a LOT more about it in a forum like this...yet I can't remember
the last time somebody said it was a flawed technology here, not counting
this discussion.



Rob