Porting MS Access application

Question

I have been asked by a friend to help him assess a number of quotes for porting a desktop application based on MS access and VBA to a web based app. The application seems to have a relatively large amount of business logic coded into the VBA.

My question is very specific - are there any good tools or resources out there which could assist the porting from access, rather than doing a complete re-write?

The end technology used for the web app does not matter hugely, but would ideally be as mainstream as possible.

Answer 1

There may be some tools to some of the basic stuff, like to upsize to a different database or maybe the look and text boxes of the forms, but converting what sounds like a lot of VBA code, not so sure.

Is this an intranet/local network type of web app or are you putting it out on the internet? Security will become a major difference between this and your Access app.

Make sure they understand Access/VBA so you can maintain the business logic that has been over the life of the Access app.

Convince your friend to stop/slow any development on the Access app to prevent the company from aiming at a moving target. This may not be realistic, but really needs to be considered.

Answer 2

You may explore the possibilities offered by Sharepoint. It may help you get the data accessible online but how well will that work depends also on how much VBA code was used in the Access application.

There are some tools around that pretend they can convert MS Access to PHP/ASP websites like DB Forms, but I haven't tried them and they usually only convert the visible part of the app and not the queries and VBA.
They can be helpful to get started though.

Random thoughts

The VBA tends to be the biggest issue. Moving to ASP.Net will take time and for that you are faced with difficult choices:

1. transfer all code to the ASP.NET to just get it working
2. rethink the structure and do a proper ASP.Net implementation from scratch.

I'd prefer the first one: just try has hard as possible to get results fast.
Use SSMA to move the data to SQL Server (unless you want to keep Access as the backend).
Make the forms look the same as on your existing application (or at least have the same function), port the VBA to VB.Net (or C# if you feel like it) form by form, module by module and test that they work as you go along.

Don't try to refactor or make things better at this stage, the point is to 'slap' the old code on the new 'system' and make it bark as it used to, not better, not worse.

Only then can you start refactoring and improving using the new tools at your disposal.

I'm saying all this assuming that there was nothing terribly wrong with the old app and that it just needed to be ported for online consumption.

If the old app was defective and wasn't fulfilling its role, then more emphasis should be placed on re-thinking which parts should be translated and which one should be reworked.

At any rate, you need to have a detailed action plan and a review of the current code and functionalities and try to limit as much as possible your expectations for the first version of the new system: avoid letting everyone input their wishes or your project will become horrendously difficult.

Concentrate on the minimum needed to achieve a certain level of functionality that will satisfy your users, then build on that.

Answer 3

Is there a reason why hosting the app on Windows Terminal Server would not suffice? This means zero changes to the app, no reprogramming cost and no danger of losing crucial business logic. If you use the Citrix extensions, you can run it in a web browser (though I guess that only works with IE -- I've never used them). But the RDP client comes in versions for Mac and Linux as well as Windows, so you can basically support anybody as long as they install the RDP client for their OS.

Yes, it's more installation on the client end, but it's a helluva lot cheaper and easier on the development and avoids the problem of losing important things coded into the Access app.

Of course, supporting large user populations on WTS/Citrix can get expensive and if the Access app is in need of re-engineering, anyway, it can change the balance. But it's something that you should consider. It's really easy to set up WTS, in fact, and provisioning a server for it basically a matter of adding RAM and Internet bandwidth (though RDP is really efficient to begin with).

One key mistake many people make when trying to run an Access app on WTS:

YOU MUST SPLIT THE DATABASE (front with forms/reports/etc., back end with data tables only), and each user must have their own copy of the front end (stored in user profile on the WTS, or in a folder on your WTS server's data partition with appropriate permissions assigned to the user groups authorized to use the app). Tony Toews's front-end updater is very useful in this context, and explicitly engineered to work in a Terminal Server environment.