Access ADP - For/Against? [closed]

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

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 2

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.