Excel® 2007 VBA Programmer's Reference

Chapter 20. Data Access with ADO

ActiveX Data Objects, or ADO for short, is Microsoft's technology of choice for performing client-server data access between any data consumer (the client) and any data source (the server). There are other data-access technologies you may have heard of in relation to Excel, including DAO and ODBC. However, these are not covered in this chapter because Microsoft intends for ADO to supercede these older technologies, and for the most part this has occurred.

ADO is a vast topic, easily the subject of its own book. This chapter necessarily presents only a small subset of ADO, covering the topics and situations that I've run across most frequently in my career as an Excel programmer. This chapter focuses on ADO 2.5. This version of ADO ships natively with Windows 2000 or Office 2000 and higher, so you can assume it will be present on any computer you distribute your application to.

An Introduction to Structured Query Language (SQL)

It's impossible to get very far into a discussion of data access without running into SQL, the querying language used to communicate with all databases commonly in use today. SQL is a standards-based language that has as many variations as there are databases. This chapter uses constructs compliant with the latest SQL standard, SQL-92, wherever possible.

There are four fundamental operations supported by SQL:

• SELECT—Used to retrieve data from a data source

• INSERT—Used to add new records to a data source

• UPDATE—Used to modify existing ...