ADP are ten times faster than Jet at some things

The primary benefit(s) of an Access Data Project is that (a) you get the stability, scalability, and security of a real SQL database, while (b) still using the well-known MS Access front-end and programming.

(a) As I’m sure you are aware, Access MDB files demonstrate a lot of problems when shared by multiple users at the same time, or when growing too large–these are limitations that SQL Server doesn’t have. Beyond that, you can secure your data and prevent corruption or loss more easily on the SQL Server.

(b) If you’ve already got the front-end built in MS Access, it’s fairly easy to let the program upload your data to the server. End-users get to continue using the same interface that they were before–it feels no different to them (except perhaps that it’s faster!). And it doesn’t require a ton of time starting the program from scratch in a .NET language (not to mention distributing/installing the new app across all your client systems).

Are there any downsides? Well, maybe a couple: (1) Clients still need the Access Runtime to use the software, and must also have network access to the SQL Server. (2) The automated conversion is not 100%–when you run the wizard to upload the data from Access, it will do its best to convert the Access-specific SQL queries to SQL Server/T-SQL ones; if some queries fail after the conversion, you will probably need to re-write them with correct T-SQL syntax.

Uh, the primary benefit of ADP is that you can control your SQL Server from within the ADP. All the benefits of upsizing to SQL Server can be had with an MDB/ACCDB linked to the SQL Server via ODBC. – David-W-Fenton Mar 28 ’10 at 23:48
Can you explain more, David? I’ve never been able to understand how an ADP is different from an MDB front-end to linked tables, except that ADP’s use a native connection to SQL Server [only], rather than the additional abstraction of ODBC. – ewall Mar 29 ’10 at 13:21
ADPs don’t use Jet, thus, the only data source they can use is a SQL Server, so no local storage (though you could use ADO to write XML files). ADPs depend on ADO for interaction with SQL Server and that is a layer between Access and the SQL Server. There’s another layer involved, too, but I forget the details. In short, there are just as many layers of abstraction in an ADP as with MDB/ODBC. And ADO can be quite problematic, e.g., trying to enforce security restrictions on base tables with views — ADO can attempt to bypass the view and go direct to the tables, thus causing errors. – David-W-Fenton Mar 29 ’10 at 17:46
Thanks for the thoughtful answer! – ewall Mar 30 ’10 at 0:33
Something else to include as an upside is that with an ADP, you get access to more SQL features (stored procedures and stuff) and your queries are stored on the server as SQL views. This usually makes things perform better if done right. It also means that your views and stored procedures are available to other clients that might want to connect to your database, instead of them being only available in the Access program. – SteveShaffer Jun 12 ’13 at 23:32

There is no reason whatsoever to port an existing working Access application to an ADP front end. Simply upsize your data and use ODBC linked tables in place of the linked tables that used to link to your Jet/ACE back end.

If despite this, you decide to port to ADP, though, keep in mind that Microsoft has been deprecating ADPs for several years now and it’s not clear if the ADP has a future or not. A2007 had no enhancements to ADPs and I believe A2010 will not, either. There is some talk that the Access development team wants to revive ADPs in the version after A2010, but that’s mostly speculation based on the team’s solicitation of comments from users of SQL Server about how Access could be improved to work with it in future versions.

ADP are ten times faster than Jet at some things. – Aaron Kempf Jul 11 ’12 at 3:44