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