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.
Best Answer
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.