So appears that’s the ODBC drivers in the click-to-run Office 2016 doesn’t install properly or it’s possible it’s incorrectly setup by default. I haven’t figured that part out yet but it was causing one of my clients trouble.
They were getting the following error when refreshing the data in some of their spreadsheets after their Click-to-Run O365 installs updated to Office 2016 from Office 2013.
Actual ODBC Driver for Open Source Databases enables you to use Excel and FileMaker Pro to easily connect to your MySQL, PostgreSQL or SQLite database. This driver includes a setup assistant that helps you get connected with the reliability and ease of use you've come to expect as a Mac user. Installing the CData ODBC Drivers on Mac OS X. The CData ODBC Driver for Impala is preconfigured for the iODBC driver manager, as are many other products like Microsoft Excel. This makes the driver easy to use with these tools. Licensing the Driver. In a terminal run the following commands to license the driver. To activate a trial license. The following sections provide instructions for installing previous versions of the Microsoft ODBC driver on macOS. Use the following commands to install the Microsoft ODBC driver 13.1 for SQL Server on OS X 10.11 (El Capitan) and macOS 10.12 (Sierra).
After some back and forth they figured out it was the data being pulled from other Excel sheets wasn’t updating and not the data from their custom order management system which seemed to be working fine.
Digging around the internet showed a lot of suggestions to remove and re-add the Excel DSN. However trying to remove the DSN presented with further errors suggesting I should reinstall the Excel ODBC drivers.
Looking for a current version driver proved difficult but eventually I discovered installing the Microsoft Access 2016 Runtime should do it. (Access and Excel ODBC drivers are apparently related). However after downloading the x86 version of the run time (you need to match your office install architecture). It warned me that it was it did not play nicely with the Click-to-Run Office installs I had. Back to the drawing board.
Odbc Excel Driver
![Odbc Driver For Mac Excel Odbc Driver For Mac Excel](/uploads/1/1/9/8/119847233/440162223.png)
Playing around with the ODBC administration CPL some more, I eventually got it to generate another error when I tried to add the currently installed ver 16 Office ODBC Excel driver.
Odbc Excel Driver Download
Googing the error I found this helpful page which gave me a hint of what I could try next. (I stole their image because I forgot to screenshot my error before fixing it.) I could try installing an older driver for Office 2010. To be exact the Microsoft Access Database Engine 2010 Redistributable which installs version 14 of the ODBC drivers for Excel and Access. This package has no conflicts with Click-to-run.
![Odbc driver manager for mac excel Odbc driver manager for mac excel](/uploads/1/1/9/8/119847233/181782782.jpg)
Now that all the errors removing and adding DSNs had gone away, I removed the User DSN for Excel Files and re added it as System DNS for Excel Files using the version 14 driver. (System DSNs are only modifiable by the administrator.)
With that done, I tested the spreadsheets once more and finally, no more errors refreshing data. Now I can go back to sleep.
Free Odbc Driver For Mac Excel 2011
Plug player for mac. Something to note: It seems that the default Data Sources CPL only shows and configures 64-bit DSNs on a 64-bit Windows 7 install. If you want to see and configure the 32-bit DSNs (which you would want to for supporting legacy processes), you have to run the 32-bit ODBC administrator CPL found here:
This shows both 32-bit and 64-bit DSNs and more importantly you can add and configure 32-bit DSN. Remember to run it as Administrator or you won’t be able to touch the System DSNs.
Using Microsoft Office for Mac as a Relational Database
By Jim Gordon, co-author of Office 2011 for Mac All-in-One For Dummies.Part 2 - Setup
Requirements
- Microsoft Office 2011, Office 365, or 2016 for Mac
- A Microsoft Excel workbook containing data tables. This file can be on a local drive, shared drive, or network location.
- [Optional] You may run queries and genrate reports from any other database application supported by ODBC drivers.
- A third-party ODBC driver must be installed on your Mac
I recommend choosing a driver based on the location of the data source file you wish to use. As of this writing there are two companies making ODBC drivers for Macintosh. Both companies offer superlative customer support and are Mac-friendly.
- For an Excel workbook file or other data source accessible in Finder on a local drive or shared drive I suggest using the Actual Access ODBC driver from ActualTechnologies. This driver works with the following data sources: Microsoft Excel workbooks, Microsoft Access .mdb and .accdb, FileMaker Pro and delmited text files. Actual Technologies makes additional drivers for SQL Server, Sybase, Oracle, and Open Source Databses.
- For an Excel workbook or other data source available via a network connection I suggest using the driver from OpenLink. OpenLink offers drivers that connect to a wide variety of data sources including Microsoft SQL Server.
- To use a Mac as a database server consider installing Open Database Connectivity Router Server. Their ODBC client software is free, but you must pay for the server software.
ODBC drivers from these two companies take different approaches to querying data. If you have the luxury of being able to choose a local, shared, or network location for your data source, you may wish to try both drivers to see which works faster with your particular data sources, queries, network speed and server configuration.
Installing an ODBC Driver
OpenLink and Actual Technologies provide clear and easy to follow instructions for installation and setup of their drivers. Be sure to read and follow their instructions and you should have little trouble installing the drivers. The drivers are available in free modes so you can test before buying.
Part 1 | Part 3 (Next) |