Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Jul 16, 2009

Pros n Cons of ADPs

By Danny Lesandrini

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

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



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date