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 whats right for your next project.
Let me begin by saying that Im probably not the most qualified person to write this article. Sure, Ive 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 Ive been able to gather from what Ive 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 isnt 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:
that ADPs apply only when your data will be stored in SQL Server.
not cost effective to convert an existing MDB to ADP.
(I only use ADPs for new application projects.)
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 cant 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 Ive never once used the Expression Builder. Its like saying, SQL Server doesnt 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 Im going to have to learn SQL Server. If this developer was already out of his depth with the move to SQL Server, its no wonder he was disinclined to take on ADPs as well. But its not that hard. Its not that big of a jump to writing T-SQL views instead of Access Queries. Its not that much more difficult to write T-SQL procs and functions instead of embedding VBA functions in Access Queries. Dont 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, lets consider the list of Cons and Pros:
- 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
- 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
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 doesnt exist. Table security may be handled through SQL Server for application and network logins.
Since one may use SQL Server temp tables or simply upsize local tables to SQL Server, this is a non-issue.
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, its not one Ive encountered in 14 years of programming in Access.
Much has been said on the newsgroups about how MDB queries can run as fast as ADP Views. To me, thats 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 youre a good Access developer, you can write Access MDB queries that work as good as ADP equivalents. But if youre 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.
In conclusion, let me be clear that I dont 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:
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.