Best practices for moving large MS Access application towards .Net?

Question

We have a really huge MS Access application developed in-house initially for our personal needs which then was turned into a commercial software and successfully sold. The software is a sort of "all-round-software-for-your-business" and contains several modules including Document Management System, Enterprise Resource Planning, Inventory Management, Customer Relationship Management, Data Analysis etc. We are quite satisfied with the current functionality of the application, but in order to meet the requests from our clients we realize that we have to move to something new.

We decided to gradually move our application towards .Net because we can stick to Visual Basic .Net: even though it is a new language for most of the developers here, we have deep knowledge of VBA and several dozen small projects implemented in VB6.

We already started moving the data layer functionality of our application to MS SQL Server, so that every data manipulation and search is performed directly on the server.

What we are looking for are best practices for gradually moving our extensive GUI (about 500-600 different forms including subforms, about 200 reports with multi-language support etc). Following the recent request from our potential customer to implement asynchronous data encryption on documents in DMS we would be also happy to completely decouple this part from MS Access and implement it in .Net.

The question is how to seamlessly integrate the .Net application with the existing MS Access system, so that we can invoke it with certain parameters (user rights etc.) and enable data exchange between this application and running MS Access application.

---

EDIT:

We tried to apply some practices from Martin Fowler's book "Enterprise intergration patterns" to achieve some integration between the MS Access application and some small utilities we implemented in .Net for various needs. But we only managed to use the "shared database" pattern and weren't really satisfied with our solution.

For instance, we implemented a small utility running as a Windows service which automatically downloads all messages from the mail server using POP3 connection and stores them into one table, whereas all attachments are stored in file system.

What we mainly did is we used ADO.NET to directly access MS Access databases in MDB format and populate table with some processed data (like the data about mail messages from the example above: we have fields for FROM, TO, CC, BCC, Subject and Body).

There is absolutely no problem to work with MDB data format from .Net, moreover we don't want to stay with MDB and upsized almost everything to MS SQL Server 2008 -- this gives us much more freedom concerning the data management and scalability.

The main problem here is that we don't know how to implement a sort of "callback" in Access so that we can trigger the execution of certain VBA code on data update.

We had great hopes with MS Access 2010 supporting update and insert triggers for data tables, but it turned out that we can only use MS Access Macros for these triggers and there is no way to execute any custom VBA code within the trigger.

We also tried some solutions with sending keystrokes directly to MS Access window to mimick some user-invoked data requery. This works, but we don't think this is a realiable solution that can be used in production.

We also looked into DDE for MS Access, but we couldn't find any good sample solution implementing DDE commands and using them for in-memory data and command exchange.

So, the main problem is to have MS Access and .Net application co-exist and interact with each other.

EDIT2:

I forgot to mention what we also implemented MSMQ library in VBA for message passing between .Net and MS Access, the problem was again the lack of callback here: we really had to poll the queue for new messages and given that VBA does not really support multi-threading it wasn't really a nice solution.

Answer 1

This is going to be a large and very involved project. I've been responsible for migrating Access databases to .NET many times, but never on this scale. I agree that a gradual approach will probably be the most realistic. Trying to take on everything in one shot is an easy way to fail.

As in other answers, the first and most important step will be to move everything to SQL Server. While doing this, document the database if this hasn't already been done, and identify areas for refactoring if they exist. Access databases that grow to meet evolving needs often have data models that can be improved upon when looking at the big picture.

Next, identify modules of the application that are "self contained". Because this is a do-everything database, there will probably be modules of it that are nearly completely disjoint from each other. After having these disjoint pieces, identify one of the smaller modules that has the most to gain from being updated, and take it on first.

While migrating to .NET, don't just do a simple 1 to 1 copy of the application. Gather input from users to identify pain points with the current application. You want your first migration unit to be a huge success to gain the complete buy in from everyone else.

After you have the first unit done, look at what happened in terms of challenges, difficulties, etc, and use this moving forward.

One thing that I would strongly discourage would be to implement partially working modules (ex: "we migrated the CRM, but features X,Y,Z are not in the new system yet"). This will cause users to quickly become frustrated from having an incomplete system when the old one to them was "perfectly fine". This kind of development may work fine for other domains, but not in businesses where users see "nothing wrong" with the old Access monolith and don't understand the migration needs.

Answer 2

Here is a kind of plan to turn your MS Access application into a VB.Net application by mutation.

This is not a general plan, the following plan depends of the project you've described.

Step 1: migrate all data to SQL Server

Do not use ODBC for connection Access to SQL Server, use an Access Project (ADP) instead. An Access Project is an Access file with an .adp extension, it has full Access features (Macros, Forms, Reports, Modules) but the connection is directly on an SQL Server database instead of an MDB file. ADP files are very compatible with MDB files. They seem to not be supported in Access 2010, while in fact the feature is hidden but it is very well supported. Like MDE, ADP files can be "compiled" into ADE files.

Migrating into SQL Server will cost few modifications in data structure and code, but all is quite easy to migrate. And it is a final target after all.

Forget about triggering database events to VBA or VB.Net code. This can technically be done using SQL Server Extended Stored Procedures, but is is a very bad trick. Your project has a future life, so it is better to enforce its Data structure by avoiding such kind of destructuring bridge. In general, play down with database triggers, it's smart but quite difficult to maintain.

Step 2: make authentication uniform

It's a good thing that your application is not bases on Access security (MDW file).

Make a plan for the target authentication for the VB.Net application, and then define a way to migrate the Access authentication to this target in order to have a uniform authentication between the two applications.

Since authentication is transverse in an application architecture, this will be more easy to slice between the Access version to the VB.Net version.

Step 3: prepare a token feature in order to make a bridge between Access and VB.Net

You said, the Access UI will have to open a VB.Net UI with some accurate parameters and also authentication. And you'll probably have to do the same in the other way.

A good solution is to build a small token feature based on a token table : columns can be a token id (integer), a token key (long string), a token date, and a parameter list (long string or text). Each time Access needs to open a VB.Net screen, then save a token in the database with the parameters, and then open your VB.Net application with only the token id and the token key. The VB.Net opens, search the token id in the database, checks the key (this applies as an authentication) and read the parameters. Then it open to corresponding form, and do what is required. Don't forget to give a duration to tokens, and to clean old tokens.

If you need to callback from VB.Net to Access (you probably will), then I think it is possible to activate some VBA code on an opened MDB Access file, using OLE.

Step 4: migrate UI and Modules screen by screen, module by module

Now the big of the preparation is done. You can start to migrate user interface from Ms Access to VB.Net.

There is no good automatic tools for doing this. VBA and .Net are too different. You have to prepare your work for such a migration. Start with a small form, like the "About" box, and continue from simple forms to complicated forms. Of course you will have to bundle and schedule some migrations, but you will gradually migrate your screens, reports and libraries from Ms Access to VB.Net.

Answer 3

I am amazed you did all that with Access. There is a very important question you not asking .NET Windows Forms or .NET WPF. WPF has a steeper learning curve but in my opinion it is more powerful with a better UI. We recently converted our commercial application from Forms to WPF and we are already getting more sales. We also added new features but the WPF UI is just sexier. Review your table design and clean it up - now is the time. Make sure you are declaring all you FKs. In Access you can add stuff on the fly pretty easy some times that stuff slips. If this is your first WPF UI build some prototypes. We could pack more into WPF that the new app has more features, less screens, and less a lot less code. You will go from hating XAML to how loving it. Consider a structure like MVVM. You may decide not to use MVVM (we did not) but at make that decision.

Answer 4

Since you already have a good understanding of the Access object model, I would think that you want to use Access Interop from your .NET app. It should (more or less) allow you to automate control of an Access app, without the opaqueness of DDE.

Answer 5

I don't know deep knowledge about your business logic layer, but you can also access your MS Access databases in your .NET application. If it's not simply about fetching data, you can implement a TCP/IP connection between your programs (VB6 and VB.NET applications). Please have a look at an article on CodeProject.

Answer 6

You are asking for a best practice on how to do the wrong thing. There is not one. Best practices encompass how to effectively create a maintainable system, so that even if a bus crashes and a whole new team needs to come in cold, the development and support can continue. The system you describe is going to send most developers running for the hills. You have product built with obsolete technology and want to interface with today's technology. And you want to do so with out addressing any of the anti patterns you have described for the core product. Those developers that are willing to tackle it would likely be unwilling to do so with the conditions you propose.

However your bus has not crashed so you can leverage the existing team that understands the system. The best way to develop gradually that I have found is using the Agile Methodology. It supports an iterative process that addresses the high risk requirements early on and addresses business needs well.

Answer 7

We decided to gradually move our application towards .Net

Often a mistake. Best practice is to not try "gradually".

even though it is a new language for most of the developers here, we have deep knowledge of VBA and several dozen small projects implemented in VB6.

Not a very good basis for a decision. If you want to learn a new language, don't learn VB. Learn C# or something even better like Java.

"a new language for most of the developers here, we have deep knowledge of VBA" is a common code phrase for "we have a VBA Guru who we don't want to irritate". That's something that's possibly bad, also.

What we are looking for are best practices for gradually moving our extensive GUI

The best practices don't involve "Gradual". They involve "Discard Entirely".

The gradual migrations that I've seen have broken down because it's so hard.

You're better off doing this.

1. Preserve the most valuable asset. The data. Move all the data to SQL Server. All of it. Rewrite all of the MS-Access to use ODBC connections to SQL Server. Right now.

2. Fire anyone who creates temporary tables or data or anything in MS-Access. All data must live in a database outside MS-Access. Data in MS-Access is what's causing the problems, so stop now and be sure that everyone agrees. If they don't agree, find them a new job that doesn't involve hacking in MS-Access while you're trying to get rid of MS-Access.

3. Find a reporting framework that will automatically generate reports close to what you have now. No programming. Just give it a table or a view and bang! it's done.

4. Enumerate all 500 reports. Partition them into reports that are easily built with the tool and reports that are harder. Prioritize the hard ones into "Must Have", "Should Have", Could Have" and "Won't Do Until Later". Replace the automated reports and the must-have reports this reporting tool completely outside MS-Access.

   My experience is that a database view often gets reused in about 20 or so reports. From that, I'd guess that you have 25 relevant views from which the 500 reports are derived. Any tool that can automate report production should handle most of your reporting from a small set of well-crafted SQL views. A few reports will be too complex or difficult for an automated tool.

5. Find a development framework that builds CRUD transactions automatically.

6. Partition your 200 forms into CRUD forms (which can be built automatically) and non-CRUD forms. Among the non-CRUD, prioritize using the MoSCoW (Must, Should, Could, Won't) rules.

   Often, 60-80% of an applications forms are simple, automated CRUD forms. 20-40% are more complex and require more skilled programming. Based on this, you have 120 to 160 CRUD forms that you don't need to rewrite, just automate. You have 40-80 seriously complex transactions.

7. Build the CRUD forms and the Must-have non-CRUD forms.

8. Evaluate the gaps. Re-prioritize and start working on the most important parts of the "Should-Have" list.

It's probably easiest to discard Access entirely. Avoid gradualism.

You're going to spend all the money eventually.

You may as well budget for it and spend it now.

Trying to do this gradually may actually be more expensive because of the complexity of trying to manage coexistence.