Pros n Cons of ADPs

I know of one Access developer who believes that the Access
MDB format is evil and that ADPs (Access Data Projects) should be used exclusively.
On the other hand, I found one well-known Access developer who believes there
are “no advantages to an ADP.” The truth, of course, lies somewhere
in-between. Neither is evil and both have their place. The trick is knowing
when to use which. A list of pros and cons will help you decide what’s right
for your next project.

Let me begin by saying that I’m probably not the most qualified
person to write this article. Sure, I’ve been working with Microsoft Access
since 1995, have thousands of posts to newsgroups, have written scores of
articles and have had hundreds of thousands code-downloads downloaded from my
site, but all I have to share is my personal experience along with what I’ve
been able to gather from what I’ve read.

Smarter programmers than me could probably tell you more
about how the JET engine works or estimate how many table scans a given query
is likely to cost. Their insight would be helpful to determine the performance
gain you could expect with one Access format over another, but performance
isn’t the only factor to consider. In this article I have also compiled the
results from searching articles and newsgroup posts on the topic but I must
warn you, there is no clear definitive answer. In a nutshell, I find the
following to be true:

1)  Remember
that ADPs apply only when your data will be stored in SQL Server.

2)  It’s
not cost effective to convert an existing MDB to ADP.
(I only use ADPs for new application projects.)

3)  Don’t
choose an ADP to achieve better performance.
(It might perform better but MDBs, when written well, perform as well or
better.)

4)  If
the new and unknown frightens you, stick with MDBs.

That last point is a reflection of the general sentiment I
found while reading newsgroup posts. There are a lot of very proficient Access
developers out there who do amazing things with ODBC and DAO. Some were honest
about simply not wanting to take on the ADP learning curve, not to mention the
transition from the Access QBE Query window to T-SQL. They are comfortable
developing solutions with MDB files and there is virtually nothing they can’t
accomplish with their current methods, so they have no compelling reason to
change.

For example, one objection read, in part, “[ADPs] do not
support the Expression Builder [that is available in the Access QBE grid.]” In
over a dozen years of building enterprise applications with Microsoft Access
I’ve never once used the Expression Builder. It’s like saying, “SQL Server
doesn’t support Macros.” Who cares? Who uses macros anyhow? People who are
comfortable with macros use macros. People who are comfortable with Expression
Builder use Expression Builder and people who are comfortable with MDB files
use MDB files.

Another poster said something to the effect of, “Now I’m
going to have to learn SQL Server.” If this developer was already out of his
depth with the move to SQL Server, it’s no wonder he was disinclined to take on
ADPs as well. But it’s not that hard. It’s not that big of a jump to writing
T-SQL views instead of Access Queries. It’s not that much more difficult to
write T-SQL procs and functions instead of embedding VBA functions in Access
Queries. Don’t let fear be the reason you reject Access Data Projects as a
potential solution. Be prepared to step outside your comfort zone.

That having been said, let’s consider the list of Cons and Pros:

Cons

  • Different paradigm requires a learning curve
  • Probably need to learn T-SQL
  • No Access Security for objects like forms, reports, etc.
  • No local tables.
  • Not able to link to ODBC sources like Excel and Oracle for
    joining tables
  • Bug with the BIT datatype and checkboxes (still unresolved)
  • Also, see Michael Kaplan’s list of things ADO cannot do that DAO
    can:
    http://blogs.msdn.com/michkap/archive/2007/07/13/3849288.aspx

Pros

  • Your database can be larger than 2 GB
  • May effectively handle more users
  • Eliminates the “Jet middle-man” potentially reducing overhead
  • May create and modify SQL Server objects from Access
  • May completely hide tables from curious users (see link for next
    point)
  • May dynamically connect to multiple SQL Server databases
    http://www.databasejournal.com/ms-access/manage-recordsets-in-adps/

Important Clarification

Security:

ADPs are not unsecured, but do not support the robust
security paradigm available with MDBs through DAO. You can supply a file
password to an ADP and you can convert the ADP into a code-locked ADE file, but
the idea of Users, Groups and object permissions doesn’t exist. Table security
may be handled through SQL Server for application and network logins.

Local Tables:

Since one may use SQL Server temp tables or simply upsize
local tables to SQL Server, this is a non-issue.

Linked Tables:

While you cannot link tables and perform joins on them, one
may use ADO to pull data from any data source into a recordset and work with
the data as an ADO recordset. If a table join is required, data from Excel
could be read into a recordset, loaded into a SQL Server temp table and the
temp table could then be joined with other SQL server tables. This is a
nuisance, but I have to wonder what kind of data you have in Excel or text
files that need to be joined to SQL Server tables. While it is a valid
objection, it’s not one I’ve encountered in 14 years of programming in Access.

Performance:

Much has been said on the newsgroups about how MDB queries
can run as fast as ADP Views. To me, that’s like saying “our butter substitute
tastes as good as butter.” The standard is still butter, or in this case,
ADP/ADO native connection to SQL Server. Sure, if you’re a good Access
developer, you can write Access MDB queries that work as good as ADP
equivalents. But if you’re not an experienced developer, ADPs are going to be
more forgiving to you. As was said by someone I deeply respect, Albert Kallal,
“ALL [ADP] SQL is 100% executed on the sql server side, and thus it is MUCH
harder to screw things up.”

Conclusion

In conclusion, let me be clear that I don’t intend to start
a war on this topic. I love MDB applications and I love working with ADPs.
But as was pointed out in the first paragraph, there are strong feelings on
both sides. Below is a pair of posts on the question about whether Microsoft is
depreciating ADPs.

From the Con Camp:

Members of the Access team and MVPs have advised me to
avoid ADP (and
ADO) unless required because: MDB and DAO is a
richer function set, it provides more flexibility, and it is much less
expensive (time consuming) to develop and maintain. ADP requires much more REAL
programming to work around the more limited feature set of
ADO.

While the ADP/ADO paradigm was recommended by MS circa
Office2000, it was a response to the slow networks of the immediate past. By
Access2003, it was no longer being promoted. Now, ADP projects have been
deprecated in Office2007 which implies that it will soon no longer be
supported. The new ACCDB extension (an extension of JET) indicates that JET
(now called ACE) is alive and well as a route to SQL.

From the Pro Camp:

It is interesting how different people at the same
company give the same but not the same advice. I still work in MDB as well as
ADP projects in 2000, 2003/XP and 2007 environments. I can say that when MSSQL
is involved the adp/ado partnership is much cleaner, faster and easier to
maintain. … and … it is apparently still in production, not deprecated.
But don’t take my word on any of this, experiment with both and arrive at the
best course of action for your particular project.

See Microsoft’s site for Office 2007:

http://office.microsoft.com/en-us/access/HA101679531033.aspx

So, which should you choose for your next project? There is
no simple answer. ADPs have value and they are still with us. Try building
your next app as an ADP and check it out for yourself. Once you get over the
fear, outside your comfort zone, you might just find you like them.

»


See All Articles by Columnist
Danny J. Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles