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 your local network, and number of concurrent users is low
• It loads slower and slower when your amount of data becomes significant
• There are locking issues when multiple users work with the same data
• It is harder to add new features, perform maintenance, and deliver updates

Eventually, it will be a pain to maintain the Access application and it is better to convert it to a 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 or 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, no fully automatic convertors exist, but the main steps are:

1. Import and migrate data from Access tables into MySQL server.
   There is a MySQL Workbench tool which is capable of migrating Access data to MySQL. Docs here
   You could dump Access data into text .sql script or use one of the convertors described on StackOverflow
   Note, there are some incompatible Access features (such as Attachments data type) so you won't be able to convert to MySQL directly!

2. Recreate Queries as Views
   Queries are used quite widely in MS Access, but MySQL Workbench will only help to convert SELECT queries. Even then not all queries will be converted because Access queries could contain parameters from linked forms or human entries. Such queries might need to be re-created from scratch. Same with any INSERT, UPDATE, or DELETE queries.

3. Recreate Forms
   After data is migrated to MySQL, it is necessary to create a DB-backed website with PHP (and your favorite PHP framework).
   Re-creating forms visually could be relatively simple with modern UI prototyping tools, however, forms frequently contain business logic (VB code as well as SQL queries with parameters)

4. Recreate Reports
   Access reports need to be recreated in a similar way as did Forms. Simple reports based on one query are easy to do with existing PHP tools/libraries. However complex reports will have to be recreated from scratch.
   Note, if you need to export your reports to Excel or PDF, there are PHP libraries for that too.

5. Rewrite custom VB code
   MS Access uses Visual Basic as a programming language, so you will need to rewrite it into PHP manually as there are no automatic convertors.
   Don't forget - MS Access could contain code in macros and modules that are not directly related to specific forms or reports.

As you see, the whole process is complex even for simple Access applications. Data might need to be restructured, Forms and Reports can be tricky, and VB code - clumsy.

What services can automate Access to PHP/MySQL migration?

There are currently no automatic converters that can do that for you.

But at Engineered IT we know how to do it 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.