Tutorial: Query Microsoft Access Tables using Excel for Mac
Part 3 - Setup and Choose Data Source

By Jim Gordon, co-author of Office 2011 for Mac All-in-One For Dummies.

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 Name).

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 use.

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 description)
    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.

Topics

Jim Gordon's MVP Home



>