Guidance for Migrating MS Access Apps to .Net Apps

Question

I will soon begin the painful*(kidding)* process of migrating multiple, separate, Access Applications to "Real" applications*(notice the quotes, no flame wars please)*. Most likely this will be Web Apps as the usual reason is multiple users and deployability but I will take it case by case.

Some of these are traditional Access apps using Access as the back end and others are using SQL Server(a central one) as the back end.

What I am looking for is a combination of your experience doing this and what resources you used to help.

Websites, apps, standards, best practices, gotcha's, don't forget's, etcetera.

I am a 1 person C# shop with SQL Server back end so whether Web or not I will be looking that direction.

Also, is it overkill or unattainable to try and develop a Framework for this kind of thing? Would there just be TOO MANY variables to even try and walk this path? Anyone ever try this?

---

Some further info based on below questions. We currently have ~250 users and they are spread between 5 Locations.

What I meant by deployability is perhaps a little vague. I simply meant that we are a Non-Profit Organization and as such we do not have the best bandwidth available so deploying full apps, even through ClickOnce can be tricky when combinded with the highly fickle nature of my users*(I want that box purple, no green, no get rid of it altogether type stuff...)*.

My idea is to try and develop a "framework", of sorts, that will help to streamline the process of moving an Access App to a .Net App.

Now I fully understand that this "framework" may be nothing more than a set of steps and guidelines; like, Use ORM*(LINQ2SQL or SubSonic)*to generate DAL, Copy UI to corresponding UserControls, rewrite Business Logic.

I am just looking for your experience/expertise to help me streamline my streamlining process... ;)

Answer 1

Those apps which use an Access database to store tables and which need web access should first be upsized to SQL Server. There is a tool from the SQL Server group. SQL Server Migration Assistant for Access (SSMA Access)

Then consider moving to the web only that portion of the app that requires remote access. And leaving the rest of the app in Access. That could save a considerable amount of time.

Alternatively consider going to Terminal Server. That along with a VPN means just some software licensing costs and next to no work on your part.

That said what do you mean by "multiple users" and "deployability"? Possibly we can give you some suggestions there. Access is multi user out of the box. However if you have mission critical data or can't rekey the data in the event of a corruption or have more than 25-50 users on the LAN then you should be moving the data to SQL Server.

Now that it's public Access 2010 can deploy applications to the web. All kinds of very interesting stuff can be done. For more information check the Microsoft Access product group blog or my blog with the appropriate Access 2010 tags

Answer 2

Speaking from experience I think you would need to upgrade on a case by case basis. Upgrading is essentially a re-write from scratch and you should take the opportunity here to re-design as necessary. The type of application structure and code style used for Access (likely to be procedural I'm guessing) is very different to a well designed OO .Net app.

You will be able to re-use the SQL Server databases of course and, depending on the apps maybe even the Access ones. If you're feeling brave you could even try the upsizing wizard although I wouldn't recommend it as we found the results less than ideal.

I would also advise you take a look at some kind of ORM tool (we use Subsonic) as this can massively reduce the amount of boiler plate code you need to write. Some ORM tools will also generate DDL for your database too.

We follow these standards (good idea to pick a standard early on and stick to it we found) and also found this really useful to get up and running.

Hope this was some help.