Convert Access Database to MySQL PHP
Migrate legacy MS Access forms to PHP and data to MySQL
When your in-house Access application grows it might become a bottleneck for your business:
- It’s limited to local network and number of concurrent users is low
- It loads slower and slower when the amount of data becomes significant
- There are locking issues when multiple users work with the same data
- It’s becoming harder to add new features, maintain and deliver updates
At some point, it's becoming a pain to maintain Access application and better to convert it to Web Application.
What are the benefits of MySQL/PHP web applications?
- High availability. Users work via browser from any device – phone, tablet or desktop
- It’s possible to open an application for your external resources (vendors, suppliers, agents, …)
- Virtually unlimited number of simultaneous users
- It can run in the cloud, no in-house servers/IT required
- Simpler development and support:
- Clear separation of PHP code and data in MySQL
- Simpler database backups
- Ability to use version control for code
How to convert MS Access database to MySQL/PHP Web Application?
Currently, there are no fully automatic convertors exists, but the main steps are:
import/migrate data from Access tables into MySQL server.
There is MySQL Workbench tool which is capable to migrate Access data to MySQL. Docs here
Basically, you could dump Access data into text .sql script or use one of convertors described on StackOverflow
Note, there are some incompatible Access features (such as Attachments data type) you won't be able to convert to MySQL directly!
recreate Queries as Views
Queries in MS Access used quite widely, but MySQL Workbench will help only convert
SELECTqueries and even not all of them.
Just because Access queries could contain parameters from the linked forms or human entries. Such queries might need to be re-created from scratch. Same about
After data is migrated to MySQL, basically it's necessary to create a DB-backed website with PHP (and your favorite PHP framework).
Re-creating forms appearance could be relatively simple with modern UI prototyping tools, however, forms frequently have some business logic (VB code as well as SQL queries with parameters)
Access reports need to be recreated in a similar way as with Forms. Simple reports based on one query are easy to do with existing PHP tools/libraries. However complex reports better to be created from scratch.
Note, if you need to export your reports to Excel or PDF, there are PHP libraries for that too.
rewrite custom VB code
MS Access uses Visual Basic as a programming language. That needs to be rewritten in PHP manually as there are no automatic convertors.
Important - MS Access could contain code in macros and modules that not directly related to specific forms or reports. Don't miss that.
As you see the whole process is complex even for simple Access applications. Data might need to be restructured, Forms and Reports tricky, VB code - clumsy.
What services can automate Access to PHP/MySQL migration?
There are no automatic converters that can do that for you.
But at Engineered IT we know how to do that right. We can provide:
- MySQL-backed database (with the option for SQL Server)
- modern, PHP-based website (with the option for ASP.NET)
- Mobile-enabled screens (work with your forms on smartphones, tablets or desktops)
- Strong single sign-in user authentication with the ability to set different access rights
- Simple user management for an unlimited number of users
- All your Forms
- All your Reports
- You will receive full source code of the website for in-house or your own cloud installation (or we could set up it on AWS for you)
We can even merge several Access databases into one single PHP Web Application.
Contact Us and we'll help you with your MS Access database.