Excel® VBA: 24-Hour Trainer

Chapter 24. Data Access with ActiveX Data Objects

The topic of data access has become one of the most intensive forces in driving the recent development of commercial software applications. Data storage and search engine companies have become the face of the worldwide voracious demand for accessing information.

Excel is without peer in its powerful features for calculating and analyzing data, and in its ability to produce customized reports in an instant with VBA. For users who deal with extremely large volumes of source data, Excel can still fall short as a data storage application. Microsoft has built Excel with some robust methods for importing external data into your workbooks, making Excel a terrific front-end application that analyzes data it does not need to store.

INTRODUCING ADO

ADO is an acronym for ActiveX Data Objects, which is the technology Microsoft recommends for accessing data in external databases. Excel's spreadsheets, being tabular row and column objects, share common features with database tables, providing a natural environment for data to be transferred between Excel and relational databases.

From Excel, using ADO you can

• Connect to most any external database in the Windows operating system, as long as that database has, as many do, an ODBC (Open Database Connectivity) or OLE DB (Object Linking and Embedding Database) driver.

• Add, delete, and edit records from a database to your workbook, or from your workbook to a database.

• Query data to return a recordset, allowing ...