VBA/Access – How Do We Upgrade?
OBSERVATION
It’s true–career software developers HATE things created in things like MS Access! Yet, there are countless instances of organizations and/or departments within them that have evolved a solution that totally solved a problem for them.
The evolution of this is not difficult to imagine. Sometimes the problem began as something small or simple enough to not require the outlay of a project and a team of developers. Often times, these types of solutions are designed by business experts in an office who have acquired basic development skills in things like MS Excel and Access.
Knowledge in these tools, combined with their working knowledge of the problems in their offices, produces a situation where the problem in the office was rapidly solved without the headache of dealing with projects and I.T. folks. This can be a good thing in terms of cost and solving problems, and it happens every day.
Sometimes though, the solution that was evolved solved the problem so well that everyone in the office adopts the solution. This usually leads to requests of the department or individual who developed the solution to improve it and add features. As the system grows in complexity and in adoption around the organization, it can become a critical part of daily operations.
WHEN ISSUES ARISE
When a home-grown solution is adopted like this, challenges can sneak up unexpectedly. For example, maybe it is an MS Access app that breaks unexpectedly, and the cause is due to a technical issue that the individual who developed this solution does not possess expertise to fix. Now the organization is at a point where daily operations are interrupted, and costs are incurred.
This is point where decisions need to be made, and believe it or not–this situation comes to us at our shop a lot.
WHAT TO DO?
Often times when these emergencies arise, the organization’s I.T. staff is summoned for counseling. If they are any good, they hate MS Access, and anything to do with developing something important on such an unstable inefficient platform. Most often, they wish to avoid trying to fix or debug something that was developed by a non-developer, and recommend developing a “real solution” as quickly as possible.
Before I go on, my thoughts on the statement above is: those I.T. folks are exactly right. You want to move off of Access ASAP if your situation is anything like this, and this system has become critical to operations.
BUT–before you scrap your Access, Excel, or VBA/home-grown system and build something in .NET, CakePHP or Java, there are some options to think about–you may actually be in a good position.
NOT SO BAD…
If you find yourself in this situation, then one good thing about being in this situation is the following: your application that you have come to depend on has likely solved your problem exactly the way the people in your office team want it solved. In other words–the app that you are trying to fix or improve, while not perfect, serves as a perfect prototype for the next place you want to move it to.
This is a good thing. Remember back to when you were first presented with your office’s problem, and you decided against going to the I.T. staff and developed it yourself? It turns out that if you had gone to the I.T. staff and made a project out of it, it had a less-than 50% chance of ever getting used. In our industry, that is the truth about I.T. projects. Often they fail because the requirements simply don’t always translate well between the subject matter experts and the I.T. team. You end up with something that “the techies” would support as being “done right” from a technical stand point, but is absolutely useless and cost a fortune.
Thankfully, you are not in this position. You have something that is totally valuable and adopted, you have a working prototype, but you have some technical challenges to deal with. In retrospect, this could be thought of as better then the aforementioned result, producing a useless but technically-sound system.
OPTIONS TO GET YOU TO YOUR GOAL
- First off, getting back up and running might still be the objective. If your I.T. team does not want to touch Access or Excel VBA code, there are vendors who specialize with these types of repairs and hold-overs
- If your home-grown app is wide in scope, perhaps you could focus on the most critical areas, and invest in some improvements to them. These can help you with the hold-over, as well as improving the concept for migrating to your next platform.
- Add some best practices around the app (if done in Access for example):
- Separate the front end app from the back-end database, if this has not been done already
- Drop in a file picker for the finding the back end database, so that everyday users are not wasting time trying to “connect to data”
- Drop in an updater module, to manage releases of your Access application
- Add your VBA code to a source control system (yes, this can be done)
- Add routines to auto-compact/repair data to reduce corruption risk
- If time is of the essence, find a provider who specializes in a rapid web development framework such as Ruby On Rails or CakePHP
- If you see the app evolving to something bigger, bring in a .NET or Java developer to port over the app
- If migration off of Access is impossible at this time, consider finding a VBA developer who can integrate with other apps through things like REST API’s. This would allow you to migrate part of your operation to a web app, and leave part of it in Access
Tags: Databases, Development, Excel, MS Access, Porting, VBA