Pros and Cons of Access Data Project (MS Access front end with SQL Server Backend)

Question

I have been tasked with moving an existing MS Access application (mdb) over to an Access Data Project (adp). Basically the Access forms will remain the same but the data will be migrated over to SQL Server. I am not too familiar with Access Data Projects so I was hoping I could get some opinions on the pros and cons of using them.

My first thought was to convert this to a web application or even a Winform application, however I really wanted to perform due dilligence in looking at Access Data Projects before making a decision. Thanks for any assistance.

Answer 1

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.

Answer 2

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.

Answer 3

I assume you're using Access 2007. If you are, there are two options when you move the data to SQL-Server:

A) Convert to adp Conversion to ADP requires you to change the DAO code to ADO code, which is another, slightly different database-library. Depending on the amount of code you have in your application, this can be a big thing in itself.

More important however, are the issues with some new Access 2007 features, that are NOT AVAILBALE when using an ADP. Microsoft already stated this will NOT improve with Access 2010. (An Example is the new ControlSource-Property for Image-Controls. It will be there in an ADP but it doesn't work!) If you want to use these, go with B)

B) convert to accdb with linked tables This will let you stay with DAO, conversion is mostly automatic and will grant you almost all features. Some complex queries may still need to be fixed, since Access can't get the same detailed information about your tables/queries when they reside on SQLServer.

The only new thing you may have to worry about, and only if you are distributing the application to end-users, are DSN-less connections or DSN-less linked tables, since creating the ODBC DSN on each end-user-machine requires admin privileges. But there are plenty of examples for this on the net.