How to centralise functionality across an enterprise?

Question

At work, we use/develop a LOT of MS Access apps.

Whenever the business has a significantly different problem to be solved, a new MS Access project is started.

As such, although these projects are solving fairly different problems, they may actually use very similar code in parts of each project. There are only so many ways you can extract customers from the same database, right? Or perhaps, many different projects need to work on a list of customers who we have had returned mail from. So, each time we start a new project, we have written the code to do pretty much the same thing that has been done previously.

Obviously, some way of "centralising" this functionality would be very useful. Say the database schema is changed slightly, we don't want to have to go and update every single MS Access app. What would be a good way to do this? Perhaps many people will say "First of all, do away with all the MS Access apps!" Of course, it wouldn't be that easy to replace all our existing MS Access apps, so it would be nice it the centralised functionality could be somehow consumed by MS Access as well. If not, I would still appreciate answers which would not work with MS Access as well.

Also, the solution would have to be in .NET as we are a .NET shop.

EDIT

We are using .NET 3.5.

References to .NET 4.0 would not be useful at this stage.

Answer 1

First step would be to create a map of what functionality is used in what application. Centralizing means you need to develop some way to track changes to applications (dependencies).

Your situation now is simple: everything is independent, so a change to one will not break another application. It is a lot of work, as the same change needs to be made to all relevant apps.

With a more centralized system, you'll need some organizational changes: someone needs to be responsible for changes to each functional area. Developers can no longer just make any change they like.

Answer 2

These kind of things are never simple from both technical and organisational perspectives.

If I had to tackle this I'd first look to see if we could be convinced that a single database or perhaps a more integrated collection of centralised databases would cater for the functional needs of the organisation.

Once happy with that I'd see if I could plan to build that database and migrate applications to using it with either overhaul of the front end's totally or modify them to use data from elsewhere (I believe this is possible with MS Access).

Alternatively it might just be best to find some big enterprise app that does all that you want and migrate to that.

I'd echo what Stephan says, that you'll need to understand the functionality so that you can prioritise and accomodate it. This will be important in determining if you end up with something a lot nicer or just a big ball of mess compared to lots of separate balls of mess.

This will also be a cultural change for your organisation since you're going to take power out of the peoples' hands and you'll need to keep them involved and "sell" the concept to them otherwise they'll not use the centralised system and will revert to building their own Access databases. :(

Answer 3

Stop the bleeding. Start thinking about that next app with a mindset of using existing functionality.

Take a look at an existing application that has pending requests to add more features. You may find existing code to use.

Start creating a more automated way to update the code in your applications. It can be difficult, but I've seen recomendations on this site for various tools to help out with vba code.

Start a policy that as Access apps mature and gain more adoption, they get migrated to the centralized app (.NET). This way, you're not wasting dev time on features to your main app that are never used (Theoretically, you should be able to develope in Access faster.). I've seen entire apps not see the light of day for various reasons. At that point, you're thankful they were built in Access.

Answer 4

+1 for the “prototype in access” idea. Access is a great tool for rapid development of an idea and reacts well to scope creep and the “hey can you also make it do this?” issues we all face.

Once the functionality is mature and locked down then you can migrate to a new app and in effect the access application becomes your spec/benchmark.

One way to make this easier is to have a semi open SQL server so adding new tables etc is not a complete tangle of red tape. If the application becomes a candidate for “upsizing” then it’s a simple matter of moving the database from the semi open SQL server to either its own server or the main supported server.

Answer 5

I've worked in a situation like just that before. MSAccess can be such a quick flexible solution that everyone can have their specific little need easily met without having to wait on a development team. To solve it properly and centralize all the development will be expensive (in time, money, and personnel). A centralized structure like that will also require a lot of rigidity that may cause people to go back to using their own little Access projects to get what they need how and when they need it.

I would suggest a code repository. You could even make it in Access and put it on a shared drive. :) Now when someone needs a specific function they can see if it exists before making their own. If your data is coming from a sql or oracle repository, have a few stored procedures or views that are compilations of datasets people commonly use. Those can be accessed from the access databases without costing the time to "recreate the wheel."

Good luck.