>

Using SQL

You can use Microsoft Jet database engine SQL to create and change both the structure of your database and the data within it. Unless you're an experienced SQL programmer, it's easiest to create your database either in Microsoft Access or with data access objects in Microsoft Excel. However, you may need to be familiar with basic SQL when you're working with data access objects. Some objects, such as the QueryDef object, include an SQL statement as part of their definition.

If you are working with Microsoft Access, you can create a query in the query design grid and change to SQL view to see the corresponding SQL statement, which Microsoft Access generates for you. This is the easiest way to generate an SQL statement, and is useful if you're not familiar with SQL. You can also write SQL code in SQL view in the Query window, or you can include SQL statements in Visual Basic code.

If you are working with Microsoft Excel, you can only use SQL statements in Visual Basic code. You may want to use Microsoft Access to create a query in the query design grid and save it. You can then open the database from Microsoft Excel and use the corresponding QueryDef object. Your code will run faster if you execute a saved query rather than one that you create on the fly.

There are two parts to SQL: the Data Definition Language (DDL) and the Data Manipulation Language (DML). Using DDL, you can create a database or alter the structure of an existing database. Using DML, you can add, change, or delete data in an existing database. Most of the time you will use DML to create SQL statements.

The following DDL statements are available in Jet database engine SQL:

CREATE TABLE statement

CREATE INDEX statement

ALTER TABLE statement

CONSTRAINT clause

DROP statement

SELECT... INTO statement

The following DML statements are available in Jet database engine SQL:

SELECT statement

INSERT INTO statement

UPDATE statement

DELETE statement

TRANSFORM statement

UNION operation

The following SQL clauses and operations are used to extend the basic SELECT statement in Jet database engine SQL:

FROM clause

IN clause

WHERE clause

GROUP BY clause

HAVING clause

ORDER BY clause

ALL, DISTINCT, DISTINCTROW, TOP predicates

PROCEDURE clause

INNER JOIN operation

LEFT JOIN, RIGHT JOIN operations

PARAMETERS declaration

WITH OWNERACCESS OPTION declaration

Jet database engine SQL also includes the SQL aggregate functions, which you can use to perform statistical operations on your data. The aggregate functions include the Avg, Count, Min, Max, StDev, StDevP, Sum, Var, and VarP functions.