• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 15. Advanced Spreadsheeting > Working with Databases

15.3. Working with Databases

Excel has much in common with database programs. Both kinds of software keep track of a list of records (like cards in a card catalog—or rows of a spreadsheet), and let you browse through those records and even perform some calculations on them. No wonder Excel is so adept at incorporating database files into its spreadsheets; Excel 2004 can access data in Web pages and FileMaker Pro databases, and may be able to use open database connectivity to access data from additional databases. Open database connectivity, usually called ODBC (pronounced "oh-dee-bee-see"), is a standard set of rules for transferring information among databases, even if the databases are in different programs from different companies.

Open Database Connectivity (ODBC)

Previous versions of Excel could access data from ODBC databases, which are standard in many corporations—but Excel 2004 can't. Although Microsoft updated (Carbonized) most of Office for the most recent version of Mac OS X, a few components didn't make the shipping deadline, including the ODBC-related components. (This discussion applies to non-FileMaker databases. Excel-to-FileMaker links work just fine (as long as they refer to File Maker Pro 6.0 or earlier files) in every version of Office 2004.)

First, since you can't create new queries in Excel 2004, you must make them in a different version of Excel, such as Excel 2001 (or, since queries are just text files, you can create them in TextEdit), and then open the query-containing worksheet in Excel 2004.

Second, to make those queries work, you must install a driver for the database you want to query. Microsoft doesn't supply drivers to Office 2004 customers; instead, you must purchase a driver from another company, such as OpenLink Software (www.openlinksw.com).

Excel 2004 also comes equipped with the necessary smarts to use Microsoft Query for Excel 2004—software that puts a graphical user interface on the task of creating queries. With this program, you can finally create and modify queries in Excel 2004—if you have the driver installed, that is. Look for it in the Applications Microsoft Office 2004 Office folder.

Once you've rounded up and installed all the required ODBC software, you can get started with ODBC by investigating the commands listed on the Data Get External Data submenu and playing with the External Data toolbar (View Toolbars External Data).



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint