Missing ODBC data sources

Question

Hi,

I have a new install of Office 2016 bought specifically for the Access app. I am attempting to link a MySQL database via ODBC with my Access. 

After installing the MySQL device driver for ODBC. The Data Source Manager sets up the source properly and tests ok in both 32 and 64 bit versions. The driver is viewable in the DSM,  but Access does not see it. Is there something I have to add to the Access installation?

Answer 1

Hi d,

How did you check device in DSM? Could you share us a screen shot?

I could link table from mysql to Access by following below steps.

1. External Data->ODBC Database->Link to the data source
2. Select Data Source->New->Create New Data Source->MYSQL ODBC 5.3 Unicode Driver->Browse a path to save the datasource
3. Enter related mysql information
4. It will create a data source file in File Data Source

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

I checked the linkage in the DSM by using the test button. My problem was that the MySQL driver did not show up in the "Create New Data Source". So I tried the Oracle one that was listed there. Sadly that took me down a false road and now I think I have to do a restore...

Now Access won't let me add a new data source as it now says I don't have admin priv's, when I actually do...

Answer 3

I don't know whether you have 32-bit or 64-bit Office installed, but you must have the correct corresponding version of the MySQL ODBC driver installed.

Also, keep in mind that there is both a 32-bit and 64-bit ODBC Data Source Administrator app so you want to make sure that you are looking at the version with the same bitness as Office Access.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 4

Actually, the “admin” message is often somewhat misleading.

If you attempt to create a file DSN, you do get a message about admin rights because the “default” location for the DSN’s is located in a folder that requires admin rights.

You can fix the above by exiting access, and then right click on the access shortcut and choose “run as administrator”.

Another way is when you use “new” for the file data source and choose the driver, then BEFORE you type in the name, use the “browse” button to say some folder in my docuemnts. (So either of above should avoid the rights issue).

I also suggest you always launch the ODBC manager from inside of Access as it will thus “choose” the correct manager (x32 or x64) for you automatic.

If the MySQL driver installer worked correctly, then as per the other thread screen shot (Edwards), you should see the MySQL driver as a choice.

So running access as admin will up your rights to save the file DSN into the "data sources" folder, but you can also “browse” to say some folder in my documents and that also avoids the rights error/issue.

Another TIP: Remember, All Access links with a FILE dsn are converted into a DSN-less connection. This is good since you can then distribute the application without having to ALSO copy the file DSN created. However, this also means that changes to the FILE DSN are ignored once the table link has been created. If you change the FILE dsn, then you have to re-link.

Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Answer 5

Thanks,

This is a good idea and best response yet. I was attempting to do a restore as I was at the point of assuming this machine was broken. But it doesn't work either. 

Thanks

Answer 6

Thank you Albert. This is a system dsn (external). The ODBC control panel sees the MySQL driver (64bit) and configures and tests it correctly. So the driver seems valid. The problem is in Access.

How do I get to the ODBC control panel from within Access? Do you mean configure the driver while Access is open?

I ran Access "as administrator" as you suggested and got the same issue.

Dave Blizzard

Answer 7

Finally, I got it to work. Thanks to Albert Kamal. here's the procedure:

- Do not use the 64-bit MySQL driver. Remove it. Install the 32-bit driver.

- Use the 32-bit control panel to configure and test. Even though it whines at you that you have admin rights problem. It works.

-Open Access as administrator from a desktop shortcut. Not pinned to start as I did.

- Add external data ODBC source and the MySQL driver shows up properly. Since you don't have to make a new one, there's no admin error.

I believe either or both Access and MySQL needs work...

Thanks Albert

Answer 8

Thank for post the solution as this help us!

Gerrit

Answer 9

I have this exact same problem, and it's driving me bonkers.

I have installed the MySQL software (on Win 10, latest version), and was (finally!) able to use the ODBC Data Sources app (the ones that appear when I type "ODBC" into the Windows Search field) to properly set up connections to my MySQL database, which is set up using PHPMyAdmin on one of my websites. I even used SQL Workbench to connect to the proper database and was able to see the table I created, and all its fields.

However, when I try to connect Access to this database via a linked table, I get to the "Create New Data Source" dialog as illustrated in the post by Edward (Tao Zhou?), there is no listing for MySQL ODBC at all (although there are many non-English Microsoft driver listings).

(Tried to upload a screenshot showing no MySQL ODBC data source but was told my account needed to be verified. Unsure how to do that.)

How can I get this data source listed in Access so I can select it?

Answer 10

Ok, there are two ODBC configuration panels on windows. The one (launched by default) is the x64 bit one.

However, you are very likely running the x32 bit version of Access.

So, you need/want to launch the x32 bit version of the ODBC panel.

The easiest way to be 100% sure, to launch Access with “admin” rights AND ALSO launch the correct ODBC panel with admin rights?

Well, right click on access shortcut, and choose run as administrator.

Now, MAKE SURE you launch the ODBC config panel from Access. (This will also ensure that the ODBC panel is launched with admin rights also).

At this point, you seem to reached the ODBC control panel, but you DO NOT see the MySQL driver when you do this (because you now launched the correct x32 panel – not the x64 one you been looking at when you launch OUTSIDE of access).

Given that you don’t see the MySQL driver, then you need to re-download and install the MySQL driver – but choose the x32 driver to download + install.

And as noted in my other post – create a FILE DSN, as they are by access assumed to be DSN-less (that means once you link the table(s), then you can deploy the access application to any workstation and you do NOT have to setup the DSN on each workstation.

However, you STILL must install the x32 MySQL drivers on each workstation, but at least by using a FILE dsn, you don’t have to setup or configure the mysql drivers on each workstation.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada