Access ADP - For/Against?

Question

I have been tasked with taking an Access 97 application and moving the back-end data to SQL Server while moving the front end to Access 2003 (using Access Data Projects). In the process of this migration the back-end data structures will be changed significantly to support new functionality.

If I had my wish we would not be using Access as the front end. I think our application would be much better served by WinForms, WPF, or a web application. We have the time needed to properly plan a business logic layer and implement an excellent solution but powers above me want to stay with Access because that is what they are familiar with.

What I could use help with is pros/cons of continuing down this path of Access development. What are some legitimate arguments for and against using Access 2003? Here is what I have come up with so far.

Pro Access:

1. Already own Access 2003 licenses
2. Easy GUI development
3. Reports look nice

Against Access

1. Having to use VBA (Visual Basic for Applications)
2. ADO vs DAO. Didn't Microsoft change things from Access 2002 to Access 2003?
3. Not tied to Access runtime
4. Choice in front end (WPF, WinForms, even ASP.NET)
5. Maintainability
6. True separation of logic from UI not possible
7. Does Microsoft still support Access ADP?

Perhaps there are other issues I am not aware off both for and against Access for application development. I am trying to keep an open mind while at the same time trying to maintain my sanity.

I have been using C# since .NET was released and the thought of going back to VBA for six months makes my head hurt. Especially when I feel I could offer so much more if allowed to develop with modern languages and tools?

Answer 1

As far as I'm concerned the only reason to stay with Access (and a newer version at that) is if you are not going to make any changes to the front end functionality and you are on an extremely tight schedule. But if you are restructuring the database and redoing some of the functionality, it makes no sense to me to stay with Access. Just making the backend SQL server will not solve performance problems either, you need to convert to using stored procs instead of the Access Jet engine.

Can you sell the idea of using what you are familiar with programming as a cost savings in the project vice going back to learn Access? Maybe if you can shave a couple of months off the time estimate, it will be reason enough to avoid Access.

If you are stuck with Access at least get them to buy a new lisence and use the latest version. It's silly to "upgrade" to an out of date version.

As far as reports looking nice - SQl Server has a reporting tool that makes very nice reports as well. Gen up some reports in SSRS and show them how nice they can be. Deployment of changes is easier in a web-based application - I'm pretty sure that older version of Access is miserable to deploy (I'm dredging back into my memory here). You end up in DDL hell if I recall. Reason enough right there to avoid it. With a web-based app (they do have an Intranet don't they?) deployment is a snap and all users are deployed at once and everything works without spending days trying to get one rogue machine to work when everyone else's version does. Nor do you ever have anyone working with an outdated version of the front end, another classic Access problem.

Show them a snazzy prototype of a web-app with a dashboard like Access can't do. Make them want the functionality they can get if they ditch Access.

Answer 2

I've read several references to a statement by someone associated with Microsoft (Mary Chipman?) that Microsoft's Access team recommends against using ADP for new development. Supposedly ADP will still be supported, but ...? If you can track that down, perhaps you could use it to persuade them to ditch Access. That seems like a shaky proposition unless they are unaware Access can link to SQL Server with ODBC ... no ADP required.

I don't see why your point #2 against Access should be a significant consideration.

Answer 3

ADPs are built around an interface, ADO Classic (a wrapper around OLEDB), that is orphaned, and not going to see further development. In A2007 and A2010, ADPs were left unchanged, which indicates that MS is likely evaluating whether or not to do to them what was done with Data Access Pages (DAPs), i.e., after two versions of no changes (A2002/A2003), remove them completely (A2007).

However, it's also possible that MS is going to do something about ADPs, as the Access team recently inquired on its blog asking for feedback from SQL Server users about what could be changed in Access to make it easier to use with SQL Server. That feedback will go into one of the next versions of Access (either the one after A2010 or the next one). This may take the form of revived development of ADPs, or it may take an entirely different form. I'd expect the latter, as the Access team is pretty firmly committed to integrating Access with Sharepoint (to great effect, I might add), and given that Sharepoint is built on top of SQL Server, I'd expect a Sharepoint-centric solution to the SQL Server "problem."

But I don't have any inside information here at all.

In your present case, you have an existing MDB already developed. Porting an existing MDB to ADP is really not a simple process -- you can't just do a SAVE AS, nor is there a conversion routine. This is because ADPs and MDBs are completely different animals. An MDB is a Jet database, while an ADP is a container file that does not use Jet. The objects in an ADP do not necessarily have the same properties and behaviors as they do in an MDB, for instance, so you can't just import them.

So, "converting" to ADP requires a near-complete rewrite, and the level of difficulty is, in my opinion, within the same order of magnitude as porting to WinForms or some other entirely different platform (though I've never used ADPs or WinForms, so I could be misestimating here). What I do know is that ADPs and MDBs are different enough that the fact that they are both Access falsely suggests that they are somehow compatible with each other or convertible -- they are not!

Given the uncertain future of the Access ADP, I would not recommend embarking on new development in that format, let alone converting an existing MDB app to ADP.

To me, it's a no-brainer -- convert to A2003 and be done with it with little or no time devoted to the process.

I would only consider the port if the payoff is big, but you've not given any list of deficiencies in the Access application itself -- all you've outlined is your dislikes in the Access development model. You might extend the timeline a bit longer and consider what the lifespan of this application is. You should also familiarize yourself with the new capabilities of Access 2010 integrated with Sharepoint 2010 and its Access Services, which allow you to develop a front end in Access and run it in the web browser. That eliminates the need for the runtime, which is a big help.

But there is no easy conversion of an existing client Access app to a web Access app. However, there is a compatibility checker that can tell you what works and what doesn't, so it's a choice not entirely without some training wheels to help guide you in converting.

Take into account the big picture of the app and its lifespan, as well as the future of Access and Sharepoint and you might come up with a completely different set of answers.

Also keep in mind that it's likely that Access won't be tied to VBA forever. I fully expect some form of .NET integration sometime in one of the next two versions of Access after A2010. On the other hand, with the new macros (which now have error handling and full branching structures), it's possible MS will remove any ad hoc scripting language from Access and provide only the vastly beefed-up macros for programming.

It's impossible to know for certain which direction MS will go with Access 5-10 years out, but we do know that there's been a huge investment in Access in the last two versions, and Access's future is now intimately tied in with Sharepoint integration. Knowing that, you may come up with a different conclusion on the relative balance of the pros and cons.

Answer 4

When you're trying to change a company's development tools, look at it from the company's perspective. Perhaps there's a couple of managers who used to work in Access. In a pinch they could jump in and fix problems, etc. Maintainability only makes sense for the corporation, not for you personally. If you write a bang up web app, but no one else in the corporation has experience in the dev tools, the corporation is worse off not better off, because they don't have more than one developer that can jump in something goes wrong, someone gets sick, etc.

I agree with HLGLM that you should upgrade to the latest version of Access rather than 2003. Since the runtime doesn't cost anything, the latest (2010) would not cost very much.

If there's ever going to be more than one developer, then Access's lack of native configuration management (version control) is a strong argument against Access.

Answer 5

ADPs are still supported but haven't had any significant enhancements for a number of versions. Thus I'd suggest upgrading the app to Access 2003 or newer and get that working on the client workstations using the runtime. Note that the Access 2007 runtime is free.

Then upwise the backend to SQL Server keeping the Access database in MDB format. Create the views and stored procedures necessary to remove the bottle necks in Access and improve performance. You'll want those views and stored procedures no matter what direction you go.

Added Do not add functionality while you are upsizing the database. Get it running smoothly first.

At this point you and the powers that be can decide what direction you want to go.

If you were to stay in Access, you would, piece by piece, add the new functionality. Making these updates available to the users every week or so. Or more often which is what I do.