Connecting Microsoft Access to MySQL
In my latest video, I demonstrate how to use Microsoft Access as a front-end for MySQL without having to create a DSN entry on the client machine. Access easily connects to MySQL through an ODBC connection but the standard practice is to create a dedicated data source under the ODBC Data Sources Administration panel in Windows (shown below). This isn’t that difficult but it’s an extra step that would need to be done on each client machine where the Access front-end resides.
An alternate method is to create the Access table links to MySQL through VBA code. The code itself isn’t that complex and I have made it available for download from Github. Under this method, you still need to download the MySQL connection drivers and install them on the client machine but this can be done as part of a standard machine setup and doesn’t need to reference a specific MySQL database as the DSN entry does.
Being able to connect Microsoft Access to MySQL is another option for the apps in which you want the rapid development features of Access with a more robust backend database solution that features such things as user accounts and group security and a greater storage capacity than the Access 2 GB limit.
The only problem I ran into while doing this is that Access doesn’t seem to like the Geometry data type in MySQL. One of the tables in the Sakila sample database I use in the example uses this type to hold a map reference and Access actually through an error when it tried to connect to the table. My solution was to create a view in MySQL that omitted that table field and then it worked fine.
If you like this video, please remember to like and subscribe to my channel so you can be notified of future videos.
