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
Choosing an ODBC Driver
- 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
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
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.