Chapter 4 -- Manipulating Data Using Queries

Chapter 4

Queries are the workhorses of database applications. You can apply them to achieve many different kinds of objectives. Queries allow you to manipulate the data in database tables. You can use them to specify the content for forms and reports. Queries can also designate the data source for a web page.

One major reason why queries are so potent is that there are so many different varieties of them. Chapter 2 illustrated how to design row-returning and bulk operation queries with ActiveX Data Objects (ADO). You can optionally achieve these and other query functions with Structured Query Language (SQL). Developers frequently use SQL to manipulate a table by extracting selected rows or columns, but these are not the only options for manipulating a data source. The classic bulk operations include appending and deleting table rows as well as updating fields. You can also use SQL to build new queries.

This chapter explores queries within Access 2000 at multiple levels.

• First, it gives a general overview of the different types of queries. What are they? What can you do with them? How do they complement one another?
• Second, the chapter describes manual techniques for building queries. The review of manual techniques serves multiple purposes. It reinforces the general discussion of query types with the steps to generate each kind.
• Third, the chapter explores how action queries automate a task. Developers can use Microsoft Visual Basic for Applications (VBA) to programmatically apply simple row-returning queries and action queries.
• The fourth approach the chapter takes to queries is a programmatic one that builds on SQL. The discussion in this chapter complements the ADO-centric discussion of views and stored procedures from Chapter 2.
• The final approach to queries deals with processing remote databases. Access 2000 is much more adept at working with remote data sources than earlier versions. Learning how to use queries to easily tap into remote data sources can dramatically enhance your solutions and your value as a developer.