Microsoft Office 2004 as a Relational Database

by Jim Gordon, Microsoft Mac MVP, June, 2007

 

With the availability of Actual TechnologyÕs Excel ODBC driver, Microsoft Office 2004 becomes a relational database program with data store and reporting capabilities. Mac Office shines at data analysis, report generation, and data presentation.

 

IÕve written a Microsoft Word document that I hope has enough information to enable everyone from beginners to experienced database programmers to use Microsoft Office as a self-contained fully relational database program. This document, along with a working database with examples and detailed documents explaining how it all works, is available as a free download.

 

Click here to download the example database  (it is compressed using ZIP format).

 

Double-click the zip file to expand it (or use free Expander).

 

Put the Databases folder into your Documents folder.

 

In the Databases folder is Examples. YouÕll find OfficeAsRelationalDatabase.doc here:

 

 

Experienced?

I think the example database and documentation has valuable examples for everyone regardless of skill level.

 

If you already know that DSN is an abbreviation for Data Source Name and that Select * means to select all rows and columns then the following might be enough to get you started:

 

Set up an Excel workbook as a relational database file. Each table should be on its own worksheet. The worksheet tab is the table name. The first row of each worksheet is the column headers (field names). Save the workbook as a Microsoft Excel .xls file.

 

Download and install the ODBC Pack of drivers from Actual Technologies. The Microsoft Access driver can use Microsoft Excel.xls and delimited text files as data sources. The drivers return only the first 3 unless you purchase them.

 

In the ODBC manager use the .xls table file as a data source.

 

Open Excel to a new workbook.

 

From ExcelÕs Data menu choose Get External Data > New Database Query

 

Microsoft Query will open. This is where you create queries.

 

When your query is created use the Return Data button to bring the query result set into Excel.