Overview

The ADOCE control provides an ADO-compatible interface to an underlying database engine using a simple subset of Structured Query Language (SQL). SQL commands are sent to the database by means of the recordset.Open method. Using SQL commands with ADO provides a simple object model while fully exposing the features of the underlying database engine.

There are two main types of SQL statements:

• Data Definition Language (DDL) Statements
• Data Manipulation Language (DML) Statements

Use DDL statements to change the database structure and DML statements to change or retrieve information. For more information on these statements, see Supported SQL Statements.

You can use SQL to create subsets of tables that meet an immediate need without requiring extensive programming. The following code example shows how to use an SQL statement.

dim rs, SQLcommand
set rs = createobject("adoce.recordset")
SQLcommand = "select * from addresses where lastname = 'Smith' order by lastname, firstname"
rs.Open SQLcommand, "", 1, 3, 1

The SQL command consists of the following commands and conditions:

• select

  The select command returns information in a single table according to the rules you specify. The columns in a table are called fields, and the rows are called records. In ADO, the table returned by the select statement is called a recordset. The select statement generates this recordset from existing tables according to the parameters that follow it.

• from

  The * from addresses command notifies the database engine to return all the fields in the addresses table. The asterisk (*) wildcard character means “all fields.” The fields specified here become the columns in the new recordset.

• where

  The where lastname = ‘Smith’ condition restricts the rows returned to only the ones containing the string “Smith” in the lastname field. The rows specified here become the rows in the new recordset. If there are no Smiths in the addresses table, the recordset is empty.

• order by

  The order by lastname, firstname command notifies the database engine to sort the records before returning them. The engine sorts the records first by last name, then by first name.

This SQL statement generates a new table using every field from the addresses table. Once the structure of the new recordset has been defined, ADOCE fills the recordset with data. In this example, ADOCE fills the recordset with data about people with the last name of Smith. It also sorts the rows of data by last name and first name, making it easy to find a particular name, such as “James Smith.”