OT: Moving a legacy MS Access db to a nix/MySQL server

[wfsp]

We have an MS SBS 2003 server with 35 users. All the clients have MS Office 2003 and now that Office 2007 has been and gone and Office 2010 is being launched we're faced with upgrading. This would cost in excess of Ł10k and I'm looking at alternatives. OpenOffice would be able to replace MS Word and MS Excel but it is MS Access that I think would be a problem. We are heavily reliant on a huge Access db which has separate front ends for various departments, e.g. circulation, finance, organisation. There is a db with “base” data and various dbs that consist mainly of “switchboard” forms consisting of buttons which open other “switchboards”. I'm not sure OO Base would be suitable.

SBS 2003 comes with, amongst other things, IIS and the ability to set up intranet web sites. I'm thinking it might be feasible to add an Ubuntu/MySQL server to the network and building a web front end using Perl through IIS (see, Perl got a mention eventually :-). There would be no access from outside so the MySQL ports could be locked down on the firewall.

For quick “one off” reports connecting via OO Base could be an option.

As an aside, I must say that for all the faults of the MS products we are using (Exchange is a complex beast) this set up has worked well for the 12 years we've been using it (and its predecessors). No one gives it a second thought, which is as it should be – it just works. On the very rare occasion the network goes down (say, loss of broadband) everyone sits round twiddling there thumbs wondering what to do. :-) It is the “cost of ownership” that has provoked a rethink.

Translating the Access db (tables, queries, forms, reports, etc.) into Perl/MySQL/HTML would be a mammoth task, risky, but I think doable. I'm well aware of the view that tinkering with old, creaking legacy dbs is dangerous (been there, done that). It is by no means ruled out that the best thing to do is spend the money.

What do you think?

[marto]

A few years ago I used a tool from MySQL to migrate from Access. It had a nice GUI, and it worked really well, MySQL Migration Toolkit. The documentation states that this is now EOL, but suggests checking out MySQL Workbench. IIRC you have experience developing using CGI::Application, from conversation and reading your posts here I'm sure you're more than capable of the doing this migration. At work I use jQuery and jQuery UI extensively for our UIs.

Regarding no one giving a second though about a currently operational system, I'd disagree that this is how it should be. As you are finding now there are costs involved in maintaining this, which are perhaps an issue. In the past you've run out of disk space a couple of times over the years IIRC. While I agree, end users should not care, someone should be wondering where systems and services are going in future.

To this end, since you'd be migrating the legacy systems into a new one you will have to design, build and maintain, is it time to ask for a raise ;)

[shevek]

Hello,

I realize you are concerned about the cost of ownership, and I also am not a huge fan of MS as well, but I don't think you should forget people cost at this point. If you were starting out from scratch I would say go open source all the way, but I assume (dangerous to do so) that you already have sys admins for the MS eco-system? You probably have a guy that is an Exchange admin? By replacing the legacy system, as opposed to upgrading it, you will also require extensive re-education of existing workers who maintain the current system to be able to maintain the new system that you propose.

What are your time-lines? How soon do you have to upgrade or replace? If you have the time, money, and people then I would say go for it. Just don't forget the retraining of the sys-admins and the users.

As you say, it is risky but doable. It depends on how much time you have until it needs to be finished.

[dHarry]

would be a mammoth task

I fully agree, it's a lot of work and comes with risk. Why do you want to change a solution that has worked for 12(!) years?? To me it sounds the alternative is (much) more expensive and (much more) risky than the upgrade to Office 2010. I think the best thing todo is to spend the money!

You must work for a non-profit organization, a lousy 10k:))

Cheers

Harry

[sundialsvc4]

I agree.   Microsoft has “got you on-the-bones for” £10,000.   And, guess what:   it would cost you more than that (and risk much more than that...) to change it.

I agree that in the long term you want to move away from Microsoft Access, at the very least as the data store mechanism.   But it is absolutely imperative that you treat this as a formal project, with a formal project-plan, a timeline, a budget of its own, and a risk/alternatives analysis.   Some players very, very high up on the command-chain in the enterprise need to be brought into the picture, because it is within their realm of concern.   This conversion is probably something that needs to be done.   The conversion is going to cost more than £10,000.   The expenditure can be quantified, and probably, justified.

You might say to yourself, “but why do I have to do all that?   Umbuntu is “free,” and I know I could just cobble something together ... ... ...”   (your voice trails off...)

Abandon all hope, ye who enter here.

“Danger, Will Robinson!   Danger!!”

Incidentally, one way or the other you will have to do this, even for the (apparently inevitable) course of upgrading the Office installations!   You have a “mission critical” app here, or at least, one that is being used every day by 35 people.   The conversion process has to go “without a hiccup,” and that, also will require a formal project plan.   Do not assume that the software will run without modification in the new environment:   this is almost never the case with MS-Access applications.   You will need a testing environment, a beta-test cycle, a rollout plan, the whole #!.   By the time all the costs are accounted-for, heck, this might be a £20,000 project... and that’s just a cost of doing business in this world.   (If 35 people rely on this thing, I’m sure that cost-justification won’t be a serious problem even with some larger figure...)

[jethro]

I know nothing about Access and I'm not even sure what you mean by switchboard forms. So strictly theoretically speaking the problem with changes like this is often the users not the software. The users who had their routine, their habits, their field of knowledge removed and substituted with something else. They will resent this unless they wanted it too or they get something out of it

One way to ease them into the new world is to give them some new capabilities or some new comfort functions that can't be done easily with the old software, otherwise they will bathe in nostalgia. For example such a comfort function could be that they can get an email whenever some specific part of the database changes or combining the database with a wiki or reports with statistics included

[Argel]

Everyone seems to be telling you to stick with Access, but when has an Access upgrade ever gone smoothly? I would suggest you use an eval copy of Access 2010 to test upgrading to see what breaks before making any decisions. If it looks like it will mostly be a smooth upgrade than stick with Access. But if it breaks a ton of things then that helps make the case for migrating to Open Source offerings.

[gnosti]

wfsp wrote:

Translating the Access db (tables, queries, forms, reports, etc.) into Perl/MySQL/HTML would be a mammoth task, risky, but I think doable. I'm well aware of the view that tinkering with old, creaking legacy dbs is dangerous (been there, done that).

If tinkering with old, creaky legacy is dangerous, then why upgrade at all? "Office 2010 is being launched," you write, but by whom? Why at your office?

If you must upgrade, could it be three years in the future, giving you time to develop your own solution?

Is it possible to test the Microsoft upgrade path for feasibility, just as you might pilot a migration away from an MS-centric approach?

Just documenting all that your system does, and that a new system would be required to do, might help planning how you might pursue either approach.

[Marshall]

This thing sounds way to big to make a "go/no-go" decision without more data. Is there some part of this that you could work on as a demo project to get an idea of what you would be getting into?