Page 507 - Microsoft Office Excel 2003 Programming Inside Out

Chapter 23

Introducing ADO

The ADO Object Model . . . . . . . . . . . . . 482 The Command Object. . . . . . . . . . . . . . . 487
The Connection Object . . . . . . . . . . . . . 483 The Recordset Object. . . . . . . . . . . . . . . 491

ActiveX Data Objects (ADO) lets you use Excel VBA to access a database. ADO relies on a
Data Provider/Data Consumer model. (See Figure 23-1.) This model is similar in concept to
the client/server model, except that the client/server model generally assumes that your data-
base operates as an independent server. This restriction doesn’t apply to ADO.

Data Request
Data Data
Provider Consumer
Data Response
Figure 23-1. A data consumer makes requests to a data provider, which returns a response
about the request to the data consumer.

Instead, ADO requires only that the data provider supply data upon request of the Data
Consumer. The actual protocol used between the data provider and data consumer is known
as OLE DB, which means that as long as a program has an OLE DB–compliant data provision
routine, it can appear to an application program as a database. The net result is that a wide
variety of programs that might not be considered database providers can be used as data
sources for Microsoft Excel Visual Basic for Applications (VBA), in addition to Excel.

Note Besides ADO, you can use other programming interfaces such as RDO (Remote
Database Objects) and DAO (Data Access Objects) to manipulate your database. Both of
these programming application program interfaces (APIs) have a lot of limitations when
compared with ADO, which is why they aren’t covered in this book. ADO is more flexible and
better supported than RDO, whereas DAO is specifically optimized for Microsoft Access
databases. Unless you have a specific requirement to use either of these APIs, you’ll be
better off using ADO.

481
Part 6: Excel and the Outside World: Collaborating Made Easy