26

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.

Alexander Galkin
  • 1,870
  • 1
  • 14
  • 18
  • Have you done a small proof-of-concept .NET app to see how well you can make the two parts cooperate? What problems did you encounter? – sq33G Dec 29 '11 at 08:42
  • How is deployed your Access application? And what is the authentication technique ? – Skrol29 Dec 29 '11 at 10:11
  • @sq33G: I editted my question to address these topics. – Alexander Galkin Dec 29 '11 at 11:29
  • @Skrol29: We usually deploy it as compiled MDB (.MDE) together with MS Access runtime. We use Windows Authentification managed through Active Directory for database connection and permissions. – Alexander Galkin Dec 29 '11 at 11:30
  • Access is a childs toy and should **never** be used in anything even remotely resembling a production environment. The limitations of it are staggering. If you need a RDBMS data store in a self-contained installation without network access or for applications on embedded devices then look at [SQLServer CE](http://en.wikipedia.org/wiki/SQL_Server_Compact) – maple_shaft Dec 29 '11 at 12:42
  • 3
    Great question. This is a very practical problem that a lot of non-software companies that grow quickly often face and gets thrown in the lap of developers - when the "do everything" Access database can't scale to their growing needs. – bunglestink Dec 29 '11 at 13:19
  • If you have huge applications, you might consider an automated solution; a manual path will be just an enormous amount of work. You'll probably find lots of "move the data" solutions, because that's easy. Finding a fully automated off-the-shelf solution to migrate the code will be harder. One thing you will need is a strong VBA (VB6) parser; these are pretty hard to find (my company has one of the few). See http://stackoverflow.com/questions/3455456/what-kinds-of-patterns-could-i-enforce-on-the-code-to-make-it-easier-to-translat/3460977#3460977 – Ira Baxter Nov 21 '13 at 18:21
  • Sadly, I've never posted here before, and can't post an answer. But I have a similar problem, and addressed it by creating a versioned deployment system in the Access db (grab and extract a CAB file), figuring out the required AppDomain manipulation to be able to load the correct CLR version into the Access process, load a .config file, and post data both ways (Send Command, Open Form, Open Report). Now I can migrate each form or report as capacity permits, or when a change is requested. Because who can stop feature development for a year to do it all? Need to sort MDI properly though. – Roger Willcocks Feb 15 '19 at 08:50

7 Answers7

18

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.

bunglestink
  • 2,262
  • 16
  • 26
  • 1
    Being able to support multiple languages will be much easier. While you should make design decisions that allow supporting multiple languages, you should concentrate as bunglestink suggested on specfic elements. I would also come up with a layout and flow for all the forms, clearly avoid linking to any form that is not 100% complete, this avoids partial functionality. – Ramhound Dec 29 '11 at 20:28
7

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.

Skrol29
  • 211
  • 1
  • 4
1

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.

paparazzo
  • 1,937
  • 1
  • 14
  • 23
  • We have developed several small .Net applications using WinForms, WPF and Silverlight (both as RIA and out-of-browser) and I agree that WPF (and Silverlight) provide much sexier look and feel for applications. – Alexander Galkin Dec 31 '11 at 15:09
0

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.

sq33G
  • 280
  • 3
  • 12
  • While I think this is a good idea, if they're using an older version of Access they may not have the level of functionality they need. – jfrankcarr Dec 29 '11 at 15:01
-1

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.

Arseni Mourzenko
  • 134,780
  • 31
  • 343
  • 513
Osman Turan
  • 99
  • 1
  • 9
  • "...enable data exchange between this application and running MS Access application." if that statement is not related to my answer. Then I simply don't know anything either. – Osman Turan Dec 29 '11 at 11:11
  • Ok. My apologies. Downvote removed. – Arseni Mourzenko Dec 29 '11 at 11:16
  • @MainMa: No problem. – Osman Turan Dec 29 '11 at 11:18
  • Thank you for your answer, I know editted my question. We do not have any problems with accessing data from .Net, both as concerns MDB and MS SQL Server. We miss the solution for dynamic data exchange between applications, meaning that we can send some data to MS Access application and it would automatically refresh the form or requery the data source. Updating the table directly in MDB is not propagated to the form, the user has always to refresh the data manually. – Alexander Galkin Dec 29 '11 at 11:28
  • 1
    I think, my answer is still valid after your edit. I mentioned about two ways. One of them direct access which is revealed useless after your edit, and the other one is make N-Tier application. I think, you should implement a TCP/IP protocol between your application. I especially recommend this method even if you want to run your applications on same machine. Because, in my old experience I've found that DDE is not very reliable for such usages and it's really annoying. One another approach for local applications can be use custom system messages (window messages). – Osman Turan Dec 29 '11 at 11:35
  • My concerns are how to implement TCP/IP server in MS Access and especially to implement callback for incoming messages. I added my question again mentioning the usage of MSMQ in MS Access. This seems to be a better solution for integration of Windows applications, but still we don't get any callbacks here. – Alexander Galkin Dec 29 '11 at 11:45
  • 1
    Seems you have a larger problem than it seems :) Because, after each my comments, you revealed something new. I'm not familiar with MSMQ BTW. Sorry. – Osman Turan Dec 29 '11 at 11:47
  • @OsmanTuran: Yeah, the problem seems to be quite common and there is no one clear solution here. We had several attempts to move completely to .Net and leave MS Access behind but had to fall back to MS Access because of some unexpected problems (mostly due to lack of qualified manpower) during last 5 years and my question is not an acute problem, it is just my daily life :) – Alexander Galkin Dec 29 '11 at 11:52
  • let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/2089/discussion-between-alexander-galkin-and-osman-turan) – Alexander Galkin Dec 29 '11 at 12:26
-1

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.

SoylentGray
  • 3,043
  • 1
  • 23
  • 31
-2

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.

S.Lott
  • 45,264
  • 6
  • 90
  • 154
  • +1 Because of this... `Often a mistake. Best practice is to not try "gradually".` – maple_shaft Dec 29 '11 at 12:37
  • 9
    This is all nice, but not realistic. Especially the part with "firing anyone" and "discarding entirely". We can't just throw away everything and start from a green field, both from financial, strategical and personal viewpoints. – Alexander Galkin Dec 29 '11 at 13:44
  • 1
    @AlexanderGalkin: The "Gradual" is nice but not realistic either. You must take a very, very hard line on using Access for storage. Moving from Access to SQL Server is often defeated by people putting "just one little table" in Access. If they do that, they cause endless problems and need to be coached or fired. I use the word "fire" to cause thinking. You can think that firing is excessive. Good. You thought about it. You aren't "throw away everything". I think I said that you have to preserve the most valuable thing, which is the data. Does that need to be in bold? – S.Lott Dec 29 '11 at 14:14
  • @S.Lott: You are right that the way of "gradual change" might is harder and I would be the first to vote for throwing the complete code base and write it anew if this were feasible... The only solution we see is to decouple step for step some part of functionality and implement it with a new technology. And this is what we can do. We seek help for best integration between these application. – Alexander Galkin Dec 29 '11 at 14:39
  • 1
    @AlexanderGalkin: My customers who tried that failed. They found that Access functionality -- when still embedded in Access -- was inaccessible. Pretty much what you're discovering. You can continue to try, however. I've provided an alternative. You can reject it as much as you want. – S.Lott Dec 29 '11 at 14:45
  • @S.Lott: I appreciate your answer a lot and will definitely show the complete discussion to our CEOs (who decide on the final solution), I just doubt that they will consider it possible, despite the negative experience... I am sorry. – Alexander Galkin Dec 29 '11 at 15:08
  • 8
    -1 Your experience is not the sum total of universe. While we would all love to live in a perfect world where we can change the culture immediately that is not reality. In addition your bias towards against some proven technologies clouds your ability to see other potential solutions. You have spelled out the best way for YOU to solve the problem not for the OP. – SoylentGray Dec 29 '11 at 15:25
  • 1
    @AlexanderGalkin `will definitely show the complete discussion to our CEOs (who decide on the final solution)` This is a huge red flag with your company right here. Effective leadership at the executive CEO level should **never** be this close to the technical design decisions. They should be establishing long and short term strategies and should be setting company direction. This situation screams of a strangling micro-manager and I don't need to explain what is wrong with that. I hope your CEO reads this comment as well. – maple_shaft Dec 29 '11 at 15:33
  • @Chad: "Your experience is not the sum total of universe". I never said it was. But my experience is the only experience I've ever had. That's the basis for my answer. "change the culture immediately"? Where did I say that in the answer? "clouds your ability to see other potential solutions"? I'm not here to see other solutions. I'm here to provide an answer. I'm not the decision-maker. This isn't public policy. We don't need a consensus. I'm providing **an** answer. – S.Lott Dec 29 '11 at 15:42
  • 4
    Sorry had to -1 this for *Often a mistake. Best practice is to not try "gradually".* - Do you have a citation for this 'best practice'? Because most people would say the opposite - http://www.joelonsoftware.com/articles/fog0000000069.html – MattDavey Dec 29 '11 at 16:01
  • 2
    "Because most people would say the opposite". Most people are smarter than the customers I've worked with. Good for them. Sadly, I've had to work with a number of customers with large, bad MS-Access applications that needed wholesale rewrites because they couldn't gradually migrate. It's my experience. That's my citation. I'm sorry my experience appears to be unique. – S.Lott Dec 29 '11 at 16:06
  • 1
    @Chad If you don't change the culture immediately and violently then it never changes. Poor management has self preservation in mind and good management inevitably gets tempted to start reversing course or bastardizing the eventual result as soon as things start getting difficult. They will inevitably pick a comfortable Hell over an unfamiliar Heaven. I have never seen a gradual approach work when it comes to cultural change, the result is just a facade. – maple_shaft Dec 29 '11 at 16:12
  • I'm not questioning your advice at all Steven :) Did you read the Spolsky article I linked to? If you know ways to overcome the problems he talks about there then I'm subscribing to your blog immediately – MattDavey Dec 29 '11 at 16:13
  • 2
    @MattDavey: Joel is not describing this case at all. In this case, the code -- as written -- is more of a liability than a value. "in order to meet the requests from our clients we realize that we have to move to something new". In many cases, the code has been poorly designed and poorly maintained. Joel's quote "As if source code *rusted*" is meant ironically. But badly-maintained and poorly-architected code does "rot" because of the accretion of poorly-made changes. This appears to be the case here. Do you have evidence to the contrary? – S.Lott Dec 29 '11 at 16:22
  • @maple_shaft - I do not disagree with that. But the question is not how do i change the culture, or even should I change the culture. The assumption it is acceptable the demand your company change to suit what you (or even the internet) thinks it should be is not realistic and will more likely end up with the OP looking for a new job. While that may be what you would prefer to his job, it is not fair for you to assume he feels the same way. – SoylentGray Dec 29 '11 at 16:24
  • 4
    @S.Lott I think it's extreme to say that the code is more of a liability than a value. Nothing the OP said indicated that it didn't work or fulfil the business needs - in fact *"We are quite satisfied with the current functionality of the application"*. There doesn't seem to be an urgent need to bin perfectly working code - no cancer to cut out. But the OP has identified that to make improvements in the future he needs to break through the glass ceiling imposed by Access - this can be a gradual process. – MattDavey Dec 29 '11 at 16:29
  • @Chad: What? "While that may be what you would prefer"... I provided the only answer that I know will work. I'm sorry you don't like it. Please upvote the answer that you like better. My role is not to agree with all the other answers. My role is provide an answer that I know will work. I know this will work. If you don't like it, you should provide a list of corrections, or -- better yet -- write your own answer that you know will work. Then folks can upvote your answer. – S.Lott Dec 29 '11 at 16:31
  • 1
    @MattDavey: "in order to meet the requests from our clients we realize that we have to move to something new". It seems extreme to me. You see it otherwise. Please provide your own answer in light of your own interpretation of the question. I have not seen gradual migration work. Perhaps you have. If so, please describe an approach that will work. – S.Lott Dec 29 '11 at 16:35