Link to home
Start Free TrialLog in
Avatar of David Smithstein
David SmithsteinFlag for United States of America

asked on

What is the best option to build web functionality for an existing MySQL database?

I'm trying to make a decision about which development environments to learn in order to extend the functionality of my MS Access app that has a MySQL database backend that usually sits on premise at my client locations.  I've been looking at Rapid Application Development platforms, but most are kind of pricy.   While these would allow mobile app development, I could also just connect my MySQL database to a responsive web design page via Apache and PHP for a decent desktop or mobile presentation as users will be company specific and accessing via VPN on a company network... usually.    What are the typical options/recommendations to interact with a MySQL database that would give an old MS Access/VBA developer a quick path to building web based data entry form solutions for an exiting database?  Are there any recommended tools out there I should be considering that are open source or low cost that would make the learning curve less steep?
Avatar of PatHartman
PatHartman
Flag of United States of America image

What do you need web access to?  Do you need to support anonymous users?

The typical Access database is used by a department or a workgroup.  Everyone works in the office and is connected to a LAN.  Are you trying to support users who work from home or on the road?  Do they need update functionality or do they just need to run reports?  If it is reports, is yesterday's data good enough?

Before you embark on a path, you really need to define your objective.  When I do this for clients, it is generally to support separate offices and/or users who work from home.  The best solution for those needs is Citrix or RDP.  Neither require modifying the Access application and both are very efficient solutions.  In fact, many of my Citrix clients report better response time using Citrix from home than what they get in the office connected to the LAN.  Clients usually run their own Citrix server but you can have a third party host it.  The problem with the third party solution is that it is all or nothing.  ALL users must be Citrix.  None will be local.  Only if you run the Citrix server in house can you have both types of users.
Avatar of David Smithstein

ASKER

MS Access is just the front end, the database is MySQL.  My front end has about 500,000 lines of VBA code, and hundreds of forms/reports. In the back end I have  394 MySQL tables, 1531 views, 1180 functions, and 11 events and together they are delivering a 26 module document control, quality management, with material management solution... it's basically a gorilla of biblical proportions.  In fact, I think I'm actually at the wall of what MS Access can handle in just the shear number of objects or VBA code as I'm starting to get "out of memory errors."

The idea was to tab into a specific module with something much lighter that can deliver just a few key management related activities, like reviewing and approving deviation incidents, with a mobile app, or a responsive web design that could work on either a desktop or mobile platform that users would access once they had a VPN connection due to this particular clients security requirements that didn't require them to install or have to wake up the whole gorilla. :-)

With a lighter and mobile friendly approach to accessing the database, if such a thing exists, then I can start producing alternate solutions within the over context of the workflows in my main software product.  Some Access developers have gone the route of Alpha Anywhere, but I had reason to doubt this approach for my needs, mostly cost related, and started looking at all available options.
Access can be made to work against an Azure database but only if you develop the app with that in mind AND have an extremely strong Azure host.  Mostly, Access apps linked to web side databases are simply too slow to be acceptable.  Microsoft has abandoned AWA (Access WEb Apps) because they were not widely adopted given that they didn't support any programming language and so provided limited functionality.

That leaves you with Citrix or RDP or something Non-Access.

Alpha Anywhere is a very pricey solution and except that it does support web access, is no match for Access.

Access can link to SharePoint lists but if you don't have SharePoint already, I would not buy it to support Access apps since I wouldn't use a SharePoint list except as a point of data capture.  If I need to capture data on-line and use it in Access, SharePoint is a viable solution but SharePoint lists are not relational databases so they have limited use in a real application.
Thanks Pat, but this is a question about web/mobile access to data in MySQL.... yes, MS Access put it there, and would remain as the “power user” portal, but if I wanted a light alternative that was web and mobile friendly to provide some limited access and functionality .....where should I be looking?
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial