Tutorial: Query Microsoft Access Tables using Excel for Mac
Part 2 - Connecting to Access

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

Choosing an ODBC Driver

You're going to need an ODBC driver to connect Microsoft Excel with your Microsoft Access database tables.
I recommend choosing a driver based on the location of the .mdb file you wish to use.
For a .accdb or .mdb file accessible in Finder on a local drive or shared drive use the ActualAccess ODBC driver from ActualTechnologies.

Installing an ODBC Driver

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.

Connecting to your Access MDB file

You need to know the .mdb file’s location before you can make a connection. The Access data source file can be a file on your Mac, or a file on a Windows shared volume.

To connect to a Windows network using SMB:
From the Mac desktop click the GO menu and choose CONNECT TO SERVER
Here’s an example of what to enter:
smb://ad;username@servername/sharename/

To connect to a Windows network using WebDAV:
From the Mac desktop click the GO menu and choose CONNECT TO SERVER
You will need to obtain the proper URL to use from the administrator of the host computer. Here’s one example, but your string could look quite different:
https://webfolders.buffalo.edu/user/u/s/username/ub/
WebDAV connections are preferred because they are very fast.

If you need to connect often you can make an AppleScript do the work. Here’s an example:
tell application "Finder"
    mount volume "smb://ad;username@servername/sharename/"
    mount volume "https://AccesFolder.domain.com/user/u/s/username/ub/
end tell

You can save the script as an application and then use the System Preferences for the user to make the application a log-in item so that whenever the computer is started the script runs and mounts the windows volumes to the desktop.

Topics

Jim Gordon's MVP Home