Tutorial: Query Microsoft Access
Tables using Excel for Mac
By Jim Gordon, co-author of Office 2011
for Mac All-in-One For Dummies.
Part 3 - Setup and Choose Data Source
Before you can connect to an
Access database, you must set the connection up in the Mac
OS ODBC Manager app (Applications > Utilities). You
give each data source a name (DSN stands for Data Source
Once you have saved at least
one Data Source Name in the Mac OS ODBC Manager app. you
access the data source in Excel. On the Data tab of
the Ribbon, in the External Data Sources group click the
Database button. Then choose the data source you wish to
in ODBC Manager - Choose User
DSN or System DSN?
You add new data sources and
name each source in the Data Source Chooser Dialog. If you
add a data source to the User DSN tab, the data source is
available to only the current Mavc OS X user account. Data
sources added to the System DSN tab of the Data Source
Chooser Dialog are available to all users of the computer.
Choose either the User DSN tab or the System DSN tab.
To define a data source, click
the Add button in the Data Source Chooser
Dialog. If prompted, choose the Actual Access driver, and
then click the OK button. Go through the Actual Access DSN
configuration wizard. You may need to unlock the control
panel with your system password (click the lock in the
lower left corner if it is locked). The idea is to build a
list of connections to your various Access databases so
you can choose from a list of database sources to connect
to using Excel or any other ODBC enabled program. The list
will appear whenever you use any program (most likely
Microsoft Excel) that wants to connect to a data source.
At a minimum the ODBC dialog box needs to know 3 things:
• The name of the database (a short name
of your choosing that you enter)
• A description (you type a short
• Your OBDC connection settings will be
saved with the the Data Source Name (DSN)
The documentation you received with your ODBC driver has
easy to follow, detailed instructions from Actual
Technologies explaining how to add data sources and name
them, should you need additional help.
Jim Gordon's MVP