Although Excel has some data management capabilities, they are no where near the capabilities of even the most elementary database product. The more data you are working with, the more evident Excel's weaknesses become. This isn't a strike against Excel. Excel simply wasn't designed to be a database. By learning how to use Excel in conjunction with a database, you'll be able to create a whole new class of solutions that would be difficult if not impossible to do using just Excel.

The easiest way to incorporate data from a database in Excel is to use Microsoft Query (MS Query). MS Query is a visual query-building tool that ships with Microsoft Office. MS Query provides the capability to design and execute queries. The query results are delivered right to a worksheet in the form of an external data range.

Although MS Query is a powerful feature, you can make it even more useful by developing parameter queries with it. A parameter query is a query that allows you to specify one or more of the criteria values each time the query is executed. You can map parameter query inputs to certain cells in Excel. Further, you can set the data range up so that when a parameter's source cell changes, the external data range refreshes itself.

For programmatic access to databases, look to ActiveX Data Objects (ADO). ADO is an object library used for working with data sources. Although ADO is an extensive object model, you only need to know three main objects: the Connection object represents the details associated with a data source; the Recordset object represents a chunk of data retrieved from a given data source; and the Command object represents a command that you execute against a given data source. Command objects are used to execute action queries, stored procedures, or parameter queries.

Because most Excel applications are fundamentally numeric in nature, I encourage you to look into Microsoft Analysis Services if you are working on a problem that involves a large amount of data. Analysis Services is a special kind of database product called an OLAP database. OLAP databases are exceptional tools to use when you need to slice and dice large amounts of data (millions of records). Analysis Services and Excel are two complimentary products. You can use PivotTables to connect to an analysis server without writing any code. For programmatic access to an analysis server consider using either ADO or ADOMD, a flavor of ADO intended for Analysis Services.

Excel's XML capabilities received a huge boost with the release of Excel 2003. As more and more data is either stored or transmitted in XML, it is likely that if you aren't already working with XML, you will be soon. In the next chapter, I'll round out my coverage of working with external data by covering Excel's XML capabilities.

This page intentionally left blank

0 0

Post a comment