An Overview
By Jim Gordon, Macintosh MVP
Updated Junue 1, 2008
There are several components of Microsoft Office that provide connections to database tables. These connections can be used for a variety of purposes such as mail merge, tables, database reports, graphs, and pivot tables in Microsoft Office Macintosh applications including Word, Excel, and PowerPoint.
Database connectivity is available in Microsoft Office for Macintosh using these technologies:
DDE – Dynamic Data Exchange connects applications to data sources
SQL – Structured Query Language provides a means to select records and manipulate tables
OLE – Object Linking and Embedding
Web Query – Connects applications to web page HTML tables
VBA – Visual Basic for Applications programming language to automate processes and develop new applications
AppleScript - programming language to automate processes and develop new applications
REALBasic - programming language to automate processes and develop new applications
WordBasic - programming language to automate processes
WordField – special way to use and format data in Microsoft Word
XML - programming language to automate processes and develop new applications. New in Excel 2004.
Microsoft Query – A graphical user interface for working with database tables. MS Query ships with Office 98, 2001 and 2004. An installer download is available for Office v.X (see below).
Microsoft Graph – An application that can use links to update graphs in Word, Excel, and PowerPoint.
ODBC – Open Database Connectivity connects applications to data sources
ODBC Add-In – Provides added functionality for automating SQL using Visual Basic for Applications. This add-in shipped with Office 98 and 2001. It is not available for Office 2004 or v.X.
ODBC Driver – acts as intermediary to connect Office applications to database tables made by Office and other applications. The last time Microsoft shipped ODBC drivers with Office for Macintosh was with Office 98. Beginning with Office v.X these drivers must be purchased from 3rd party providers.
Warning: VBA support has been lost in
Office 2008. It will return in the next full version of Office.
Meanwhile, use Excel 2004 or NeoOffice.
Word 2008 directly
supports everything above except for ODBC, VBA and XML. ODBC can be
used with Excel
2008 and Word’s Data Merge Manager can import the resulting data.
Excel 2008 supports everything above except for VBA and the ODBC Visual Basic add-in. 2008 has better XML support. Excel makes a good data source for Word’s Data Merge Manager. Microsoft’s article “Import data into Excel using ODBC” explains how to make the connection.
PowerPoint 2008 Microsoft Graph is lost. DDE and ODBC do not work directly in PowerPoint.
Word 2004 directly supports everything above except for ODBC and XML. ODBC can be used with Excel 2004 and Word’s Data Merge Manager can import the resulting data.
Excel 2004 supports everything above except for the ODBC Visual Basic add-in. Excel can open and save in Excel XML format but has limited XML capabilities. Excel makes a good data source for Word’s Data Merge Manager. Microsoft’s article “Import data into Excel using ODBC” explains how to make the connection.
PowerPoint 2004 has OLE support and links work with Microsoft Graph. DDE, ODBC, and XML do not work directly in PowerPoint.
OLE in Office 2004 is “level 1.” Many features in OLE for Office for Windows do not work or require different steps in Office 2004.
Visual Basic For Applications (VBA) has version 5 support.
Microsoft Query is included with the standard install of Office 2004. At last it is not necessary to install this from the Value Pack or download it separately.
Microsoft Graph is included with the standard install of Office 2004. In previous Office versions you had to install this from the Value Pack.
Microsoft Query X for Microsoft Office v.X is available as a download from Microsoft. It was designed to use specific ODBC drivers. For details about how to obtain MS Query click here to be taken to Microsoft's web site. Be sure to follow the directions on the link concerning updates as well as where to obtain ODBC drivers.
Office 2001 installer does not have the ODBC drivers that are necessary to use Open Database Connectivity. The Office 2001 Value Pack does have Microsoft Query, which you must install before you can use the ODBC drivers which are now available for download. Install MS Query from the Value Pack before installing the drivers below.
On February 12, 2002 Microsoft released drivers for Office 2001. You may be able to download the drivers from the Microsoft Download web site located here: Click here.
1. Close all applications
2. Install Microsoft Query
Insert the Office 2001 installer CD into your drive
Double-click the folder called Value Pack
Double-click the icon of the Value Pack Installer
Put an X check by these two items: Microsoft Query and Programmability
Click Continue to finish the installation
3. Install Microsoft Query
Office 98 installer's Value Pack includes everything necessary to use ODBC. Install MS Query and all Data Access components including the supplemental help files from the Value Pack. The following drivers are provided:
dBASE
Visual FoxPro
SQL Server (version 7)
Oracle (version 7)
Delimited Text Table
A separate tool was available to work with
FileMaker Pro.The
FileMaker Pro tool for Office 98 seems to be no longer available for
download
from Microsoft..
Cick here to download the Appropriate Service Release for your version of Office
Run the updater
Restart your computer & rebuild the desktop file by holding ALT + OPTION keys down until prompted to rebuild the desktop file then click OK when prompted.