Programming Excel with VBA and .NET

Working with ADO and DAO

If you want to be able to manage all the details of working with data in your worksheets, you can manipulate data programmatically using one of the two programming interfaces: ActiveX Data Objects (ADO) and Data Access Objects (DAO).

DAO came first. It was developed in conjunction with Microsoft Access and is the native programming interface for the Jet database engine, the built-in data engine for Access. ADO came later, incorporating some of the database cursor optimization that came with Microsoft’s acquisition of FoxPro. It is more flexible, better suited for high-performance applications, and designed to be more neutral in dealing with different data sources. But, truth be told, many experienced and respected Access developers still do most of their work in DAO.

To use either of these programming interfaces in Excel, you need to add a reference to the appropriate object library. On the Tools menu in the VBA programming environment, select References, then select the appropriate object library from the list.

A full discussion of ADO and DAO is beyond the scope of this book, but we will touch on some of the key objects and members of each interface.