If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Advice - PHP/mySQL CRM app with offline access

I have a client that is using an in house developed DB for all their ERP needs, everything from sales orders to accounts payable, from quotes to inventory management and everything in between.

This database was developed on the MS Access 97 platform and they still use Access 97 to work in it of course. After some conversations with them, this is what they want:

1. More robust database back-end that does not have to be compacted on a regular basis.
2. An easier way to deploy the database to employees than installing Access 97 on each machine, with the Access DB frontend.
3. A more efficient way to manage access control (making sure shipping sees what they need and financials sees what they need, etc.).
4. A way to work "offline" and sync when connected to the network, either in house or over VPN. This is for field sales team.
5. Ability to pull over all tables, reports, queries, etc. from the existing database

My idea is:
1. To to build a web application on their web server,
2. Make it accessible via "https" only
3. Make the web application accessible via internal IPs only
4. Somehow make the web application available "offline" for sales people and auto-sync when it detects the server address as available.
5. Give it the ability to import data via CSV, Excel, etc.

My question is does this sound like the right place to start and what advice do you have for moving the access db structure over to a new mySQL db? I am familiar with web design and development, I have worked with PHP and mySQL many times, just never had to work with Access 97 and mySQL.

Any help is greatly appreciated. Let me know what you need to know in order to point me in the right direction

4. Somehow make the web application available "offline" for sales people and auto-sync when it detects the server address as available.

That sounds possibly problematic to me. I would probably opt to either just make it available over the internet with some good, best-practice user account admin stuff (and using HTTPS as you already stated), or else make sure they all have the ability to connect to a VPN that would allow them to remotely connect to the intranet whenever they need access to the app (leaving the security best practices to the VPN you [choose to] use).

"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation

That sounds possibly problematic to me. I would probably opt to either just make it available over the internet with some good, best-practice user account admin stuff (and using HTTPS as you already stated), or else make sure they all have the ability to connect to a VPN that would allow them to remotely connect to the intranet whenever they need access to the app (leaving the security best practices to the VPN you [choose to] use).

Agreed, that is a place of concern for me. Outside of building a custom app, which places them in the same boat of installing software to each workstation/laptop, I am unsure about this one. I may just advise them against this after I find out exactly how often are the sales people not near an Internet connection, my assumption is rarely.

Any ideas on moving the data, tables, queries, etc. from Access 97 into mySQL? Thanks for your reply.

Agreed, that is a place of concern for me. Outside of building a custom app, which places them in the same boat of installing software to each workstation/laptop, I am unsure about this one. I may just advise them against this after I find out exactly how often are the sales people not near an Internet connection, my assumption is rarely.

Any ideas on moving the data, tables, queries, etc. from Access 97 into mySQL? Thanks for your reply.