SQL commands are sent to the database by means of the Open method. The Open method opens a Recordset object based on the SQL command sent to the database. The recordset that is returned can be read, sorted, and modified.
There are two main types of SQL statements:
DDL statements are used to change the database structure, and DML statements are used to read, sort, and modify information. For more information on the statements supported by SQL, see Supported SQL Statements.
Using SQL Statements
SQL is used with ADOCE to create a subset of existing tables that can be queried and updated without an extensive programming effort. This subset is instantiated in the form of a Recordset object. In the form of a Recordset object, data can be read, changed, and deleted. Information is returned from the database based on the rules specified in a SQL query. In ADOCE, the table returned by a select statement is called a recordset.
The following code example shows how a recordset is opened using a SQL statement:
dim rs, SQLcommand
set rs = createobject("adoce.recordset")
SQLcommand = "select * from address where lastname = 'Smith' order by lastname, firstname"
rs.Open SQLcommand, "", 1, 3, 1
The previous SQL statement generates a recordset using every field from the address table. Once the structure of the new recordset is defined, ADOCE fills the recordset with data. In this example, ADOCE populates the recordset with records that contain the string "Smith" in the lastname column. 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.” The SQL command in the previous code example consists of the following commands and conditions:
The select statement generates a recordset from existing tables according to the parameters that follow the statement.
The * from command notifies the database engine to return all the fields in the addresses table. The asterisk (*) wildcard character refers to all fields. The fields specified in the SQL statement become the columns in the new recordset.
The where condition restricts the rows returned to only ones containing the string specified in the SQL statement. The rows specified here become the rows in the new recordset. If there are no fields that match the query in the address table, the recordset is created with empty rows.
The order by command notifies the database engine to sort the records before returning them. The engine sorts records according to the specified columns. If multiple columns are listed, each successive column sorts the recordset. In the previous example, the recordset is sorted by last name, then further sorted by first name. Up to three columns can be specified in the order by statement.