Yes, Microsoft Access Works with MySQL Databases

​Yes, you can use Microsoft Access as a frontend to your MySQL database. MS Access is not limited to pairing with Microsoft SQL Server.
 
Microsoft Access is an exceptional tool for building user-friendly database applications. Access is one of the top selling rapid application development tools on the market. The programming tools Microsoft includes in Access mean less code writing. This reality just simply saves programming time. In addition, average users can learn to do basic things with their own data because they can use wizards instead of writing code.
 
However – there are still a lot of misconceptions about MS Access capabilities. One of the biggest misconceptions is that Access can only be paired with Microsoft products like MS Excel or SQL Server. These misconceptions are just simply false. One of the things that makes Access such a great user-friendly data management tool is its ability to integrate data from a wide variety of sources. Your organization is not limited to using MS Access with only Microsoft products.
 
It is not uncommon for smaller organizations to use open-source data management tools. MySQL is one of the more often used open-source database applications. And – yes – you can use MS Access with MySQL. Following is one example of how a small organization might make use of pairing MS Access with MySQL.

Why use a SQL Database?
 
Firstly, it’s important to understand the relationship between MS Access and SQL Databases. Data management projects have multiple components. One of the most important aspects of managing data is where and how the data is stored and secured. The easiest way of storing data is to use a spreadsheet application. Information is simply inserted spreadsheet columns.
 
However, as information complexity and volume increase more robust data storage capabilities are needed. That is why databases are so important. Databases make it possible to store information in a relational table structure. They use multiple tables to store associated information. Data tables have relationships with each other allowing for enforcement of referential integrity between tables. For example, a relational database structure makes it possible to enforce cascade update between joined tables such as updating related customer orders when a main customer record is updated.
 
Microsoft Access is a relational database. You can build tables within Access and you can set up relationships between associated tables. Yet, Access tables are not nearly as robust as SQL for storing table. Access cannot store the same volume of data as SQL. Nor are Access tables as secure as SQL tables.
 
The next step up the evolution of data management is to move data to a SQL backend and use MS Access for the frontend user interface. Access is exceptional when it comes to building data entry forms, reports and queries. There isn’t a better desktop database solution for building user-friendly frontend applications than MS Access.
 
By linking MS Access to MySQL tables, it is possible to have robust data storage capacities married with robust user interface capabilities. In addition, this model allows for additional hybrid capacities. Using a SQL backend means it’s possible to build web-facing applications as well. In the end SQL gives an organization more flexibility and expanded data accessibility.
 
How to Use MySQL in Conjunction with MS Access
 
To use MySQL with MS Access you must first import all your data to MySQL. Then once the data is in MySQL you can link the MySQL tables to your MS Access frontend. Following is a general overview of setting up a hybrid MySQL/Access application. The over-view is meant for education, rather than step-by-step instructions.

• Create the MySQL Database: If you already have an Access database, you’ll want to use the Access export features to get your data into MySQL. Before exporting Access data, you’ll need to set up your MySQL database. This can be done on your in-house server, or you can use MySQL tools that may already be installed through your website hosting solution. The advantage of using a MySQL database already integrated with your web hosting service is that the data will be available for web-facing applications. To build a MySQL database through the web host:
  
  • Find MySQL Databases on your web host control panel. Use this capability to create a new database.
    
  • After the MySQL database is created, look for phpMyAdmin on your control panel. phpMyAdmin is the user interface. It is here that you’ll be able to interact with your database, see all database tables, manage the various table columns, primary keys, table relationships, etc. 
• After creating the MySQL database, download a MySQL ODBC Connector. This connector is very important. It serves as the vehicle to connect your MS Access application to the MySQL database.
• Once you've downloaded the MySQL ODBC Connector, search your start menu for ODBC Data Source Administrator. Use the ODBC Data Source Administrator to set up a connection to your new MySQL database. You’ll need to enter the server (your website address), database user name and password. Store the ODBC Connection file on your network.
• Now that you’ve created a MySQL database and corresponding ODBC Connection file, you are ready to export your MS Access data into MySQL.
  • Create a copy of your MS Access database as a backup.
  • Open the Access database and bring tables up in your left-side navigation pane. Then follow the instructions below:

• Once you’ve successfully exported all applicable tables to MySQL, open your MySQL database and really review every table. Make sure primary keys are properly set, check data types on your fields, make sure table relationships are all in order. If you’re using phpMyAdmin to interact with your MySQL tables, you should be able to do all this maintenance from phpMyAdmin.
• After you have cleaned up all the MySQL tables, you’re ready to link those tables back to the original MS Access application.
  • Make sure you have a backup copy of your Access application
  • Then delete all Access tables that were exported to MySQL
  • After you’ve deleted all exported Access tables, you’re ready to link the new MySQL tables. See the diagram below.

​Moving data to MySQL is a full fledged migration project. The steps outlined above are merely an overview of the process. For instance, once tables have been exported to MySQL, there is real work in making sure all primary keys are properly set, table relationships are in place, table column data types are properly checked and set, etc.
 
Migrating your data to MySQL has a lot of advantages. You will be upsizing the data storage capacity of your database. In addition, if you host your MySQL database with your web host, you’ll be able to access the data through a web interface.
 
But, like any other data migration project, you may find it necessary to rework some of your Access forms or reports. Putting the data in an off-site SQL database means your forms, reports and queries must be efficient. If forms, reports and queries are not built to high efficiency standards, you will get a slow-down in performance. Migration projects can be very successful, but they must be done properly for end-users to feel the full benefit.
 
If you’ve any other questions about migrating your data to MySQL do feel free to contact me.
 
For More Data Management Articles See:

• Using Microsoft Access for Data Evolution Projects
• Yes – Microsoft Access works in a Multi-User Environment
• YES – Microsoft Access Can be Used Securely
• Deploying Microsoft Access in a Remote Environment
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....