Chapter 23: Introducing ADO

---

Overview

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 database operates as an independent server. This restriction doesn’t apply to ADO.

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.

---