mysql db as master, access mdb's updating from it - how?

Question

I am helping a friend to streamline his little manufacturing business db. Currently, they have

• An address db
• bookkeeping db
• order mgmt db

All the three are mdb files. They can't pay me really, so I am doing good will work. Thus, just doing the barely needed. It would be ideal to merge order mgmt and address, but that's maybe for later.

What is needed now: Of course, the address db is used by their sales, their accountant, the order mgmt guy. So it's a mess. I was proposing to have a master address db on the internet, so that the sales man can always access and edit it anywhere. He is also the master for editing. The other guys would just update their local dbs from this master one. And I thought that mysql could be used as the master, with a simple php interface to enter/mod/del data.

Is this a good idea (I have only marginal experience on access)? If yes, I suppose I could connect to mysql via ODBC - could this work to only download incremental changes? (The address mdb is just 1.7 MB, but the internet link is weak and sometimes breaks).

BTW, this is a very simple solution for non-tech-savvy guys - is there any simple, cheap (or even free) and open source solution which could replace this rather historically grown mess on Access? It's just managing addresses, managing orders, printing labels, and some simple bookkeeping (I am happy to start a new question if this overloads the other ones). They are open for changes (one guy actually is pretty interested in open source solutions and pushing the others for it).

Thanks a lot for your suggestions.

Answer 1

If you wanted to port the data to an online DB (like MySQL) and then write interfaces for the different folks to have access to their respective parts of it then this job is becoming too big for a favour.

If it's worth doing, it's worth doing properly. Tell your mate he really needs to invest a bit of cash and get the thing sorted out correctly.

I don't see the point in bodging his Access databases any further.

Answer 2

As Evernoob said, porting this to a decent web app will not be trivial. When you list the three different "db's" I'm wondering if these are just tables within one Access mdb file? My suggestion for a quick, easy solution would be to clean up the Access database and use something like Citrix, or Terminal Services for multiple users to have the ability to work in one single Access database (and not have the data traveling across a network). I had an Access database app. that had 20 concurrent users using Citrix.