none
Access 2019 - MySQL ODBC Driver Not Showing on "Create New Data Source" - SOLVED RRS feed

  • Question

  • The Original post is at the top - the solution I found is below. You probably need to read both.

    THE GOAL: Connect a Microsoft Access front end via linked tables to a MySQL database created on one of my websites (using PHPMySQL). I am currently using the latest available versions of Win10 and MS Access (Office 365) as of August 2019.

    THE PROBLEM: I go through the entire process in Access as follows:

    • External Data > New Data Source > From Other Sources > ODBC Database
    • in the subsequent dialog, select "Link to the data source by creating a linked table" and click OK
    • in the "Select Data Source" dialog, click the Machine Data Source tab, then click the NEW button
    • in the "Create New Data Source" dialog, click the "System Data Source" radio button and click NEXT
    • This brings up the "Create New Data Source" dialog, with a "Select driver..." list box.

    Every source and tutorial I've found so far shows the MySQL ODBC driver already appearing in this list box, but that driver does not appear in my list box (although a lot of non-English Microsoft drivers do appear there).

    WHAT I'VE DONE SO FAR:

    • I created a database, table, and appropriate fields on one of my websites, using CPanel > PHPMyAdmin. The table is called "ReadingLog"
    • I have gone to MySQL.com and installed all their recommended tools
    • I have been able to successfully connect to my new MySQL database on my server using both the 32-bit and the 64-bit ODBC data sources
    • I have been able to successfully connect to the database using MySQL Workbench, and to see the ReadingLog table and all the fields (columns) in it.

    MY QUESTIONS:

    1. How can I get the MySQL ODBC driver(s) to appear in the MS Access "Create New Data Source" driver list box?
    2. What else am I missing to get MS Access to connect to and link tables from this MySQL database (that is NOT on my computer)?

    Thanks in advance for any help you can offer. I'm totally stumped at this point.

    =================================================

    UPDATE - THE ISSUE IS SOLVED. Here are the steps I took to solve the issue, as near as I can remember. There have been a LOT of them, and a large percentage were non-productive, which you don't need to hear about. The steps listed below are in addition to the steps listed above.

    1.  I set up a remote ODBC connection on my server, where I had already built the database and a table to go in it. To do this, I followed the instructions here: https://www.a2hosting.com/kb/developer-corner/mysql/remote-mysql-connections

    I am about 80% confident this was a necessary step, and the referenced page was the only place I found that told me it was necessary and showed me how to do it.

    2. I installed the MySQL Workbench, along with the 32-bit and 64-bit drivers (I thought), on my computer. I am not convinced this was necessary, because of the next step.

    3. I installed (reinstalled?) the MySQL ODBC 5.1 Driver (32-bit) on my computer.  I downloaded the 32-bit driver msi file from https://dev.mysql.com/downloads/connector/odbc/5.1.html

    4. I used my MySQL database credentials in the ODBC Data Sources (32-bit) app to successfully establish a connection to my remote database.

    5. FINALLY, I was able to connect to the remote database in MS Access. The MySQL ODBC 5.1 driver appeared in the list of data sources, and to complete this connection, I had to re-enter my credentials, which makes me wonder if step 4. above was necessary.

    ----------------------------------

    A huge THANK YOU to Daniel Pineault for his patience and links, and even more for his suggestion to create a DSN-less connection, which I am beginning to understand is what I will need to do if I want to distribute my Access front end to other users so they will be able to connect to the same MySQL database.

    There is still a LOT yet to learn about this, but HOORAY it's working! And I hope my description here can help someone else avoid the frustrations I've endured to get to this point.


    • Edited by DPM39560 Saturday, August 10, 2019 4:00 PM
    Saturday, August 10, 2019 10:26 AM

All replies

  • What is the bitness of the MySQL ODBC Driver?  What is the bitness of MS Access?  Did you match it to the bitness of MS Access?

    Why not create a DSN-Less Connection instead?  See: http://www.accessmvp.com/DJSteele/DSNLessLinks.html


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Saturday, August 10, 2019 11:12 AM
  • Thanks for your response, Daniel;

    As I indicated in my original post, I have made successful connections with both the 32-bit and the 64-bit MySQL ODBC data source apps.

    As for Access, I am running 64-bit Windows 10 and have installed Access from Office 365. I am running the 32-bit version of Access. Does this mean I should UNINSTALL the 64-bit MySQL ODBC data source app?

    I will look into the DSN-less links as well.

    Thanks again - I'm still trying to wrap my mind around all this info.

    Saturday, August 10, 2019 11:32 AM
  • No,  you don't really  need to un-install the x64 bit drivers. Likely the MySQL workbench needs and uses hte x64 bit drivers.

    As for a DSN less connection?  Access does this for you automatic if you create + use a FILE dsn. Once done then the FILE dsn is ignored. You can even distribute the front end application to additional and NOT have to setup a DSN (but as always, you will need the mysql drivers installed on each workstation).

    So just keep in mind that Access will automatic create DSN-less connections for you if you follow the defaults and create a FILE dsn.

    Regards,

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Saturday, August 10, 2019 5:11 PM