Visual Basic 6 (VB6)

SQL (pronounced "sequel" according to the most definitive source I can find) or Structured Query Language is the standard programming language for relational databases. Unfortunately, it's only as "standard" as the particular database engine in use. Each database engine can have subtle varieties that can be problematic if you need to move the code in an application between databases (such as Jet and MS SQL Server). On the other hand, the differences are mostly minor syntax variations - the basic structure of the language is similar in all implementations.

On this page, I'll focus on Jet's SQL dialect and show you what you can do with SQL (almost everything), what you can't do with Jet SQL (there are several key things that can't be done), and where SQL works better than Jet Data Access Objects for working with a database. If you need a little background on building databases, you may wish to pay a visit to my Introduction to the Relational Database Model page or my Fundamentals of Database Design page.

• Introduction to Jet SQL
• Constructing a Database with SQL
• Querying the Database with SQL
• Managing Data with SQL
• Limitations of Jet SQL
• Summary

The Visual Basic and Access help files provide fairly complete instructions for using SQL statements. (Assuming you know what statements you want to use.) In fact, most of the content on this page is based directly on information available in the help files. Hopefully, I've organized it in a way that makes it a little easier to understand.

---

Introduction to Jet SQL

---

Although Jet provides an extensive library of programmable objects for managing a database, SQL is the true programming language of databases. Almost everything you can with DAO code, you can do with SQL.

• Construct a Database
  Use CREATE TABLE, ALTER TABLE, and CREATE INDEX.
• Create a Relationship
  Use CONSTRAINT with CREATE TABLE or ALTER TABLE.
• Copy a Table
  Use SELECT ... INTO.
• Insert records
  Use INSERT ... VALUES or INSERT ... SELECT.
• Update Records
  Use UPDATE ... SET.
• Delete Records
  Use DELETE.

Before you can do anything with Jet SQL, however, you'll need to have available a reference to a database object. All of the examples on this page will assume that you've created and set the database object reference elsewhere so that you can use the Execute method to run a SQL statement. If you need help getting started with DAO, visit my Jet Data Access Objects page or the Introduction to Jet and Access page.

If you desperately want to avoid dealing with DAO code, you can "cheat" and just use the Database property of the lowly data control. I don't recommend this for any real production code however, since you'll be replacing about five lines of DAO code with a hefty custom control.

Unlike Jet Data Access Objects, SQL makes a clear distinction between the language used to create database objects (Data Definition Language or DDL) and the language used to manipulate data (Data Management Language or DML). In the next two sections, we'll look at SQL for creating a database and working with data.

In all of the code examples, I'll be using a database object - db - that's assumed to be created elsewhere. Keep this in mind when you see the statement db.Execute. I'm also going to operate under the assumption that you have the good sense to name all of your database objects with names that don't require you to use square brackets everywhere. An easy rule is to give objects names that would be valid identifiers in Basic code. Jet limits the length of most identifiers to 64 characters, but you should try to use much less. (If you think you might ever upsize to SQL Server, use identifiers of 30 characters or less).

---

Constructing a Database with SQL

---

SQL Data Definition Language (DDL) is used to build database objects. DDL consists of four basic SQL statements: CREATE TABLE, ALTER TABLE, CREATE INDEX, and DROP. CREATE TABLE and ALTER TABLE are used not only to create tables and fields, but also to create relationships using the CONSTRAINT clause. CREATE INDEX does exactly what you would expect, it creates an index on a field or fields. The DROP statement is used to delete objects.

• CREATE TABLE
• ALTER TABLE
• CREATE INDEX
• DROP Statement

CREATE TABLE

Let's look first at the general syntax of the CREATE TABLE statement:
Note: Items in square brackets [] are either optional or not always required. Let's first break that down a little and explain the components in plain English.

CREATE TABLE tablename

This part is simple enough, just substitute the name of the table you wish to create for "tablename".

Creating the Fields

Following CREATE TABLE tablename is the list of fields to be created where the entire field list (also including any CONSTRAINTs added) is enclosed in parentheses. Each field is defined by specifying the field name, the appropriate keyword for the field data type, the size if necessary, and optionally a primary key, unique index, or foreign key constraint.

Let's look at a simple example before examining how to create each type of field:

The final step for this in an application would be to actually have Jet execute the SQL statement. This is done using the Execute method of the Database object. In the code sample, you can assume that the variable sSQLCreateTable is a string variable which has been assigned the above SQL statement. Here's the necessary DAO code: That's it! Essentially, only two lines of code are going to do the entire task using a SQL CREATE TABLE statement: one (or more) to assign the SQL statement to a string variable, and one to call the Execute method. If you're feeling daring, you can even enter the SQL statement as a literal string in the Execute method (although I wouldn't recommend it for debugging purposes).

IMHO, using a SQL statement and the Execute method is a considerably simpler method of creating a table than using the DAO library. Using straight DAO code, you would need to call CreateTableDef to get a reference to a TableDef object, then repeatedly call CreateField for each field, using the Append method to add the fields to the table and finally using the Append method to add the TableDef to the database's TableDefs collection. However, there are some things (most unfortunately) that you can't create using the SQL CREATE TABLE statement, so you might need the DAO code anyway. If you need to set properties such as the default value, validation rule, etc., some DAO code will be necessary, but you might find it easier to create the basic table structure using a SQL statement then just set the extra properties with DAO since at that point the TableDef object and all the Field objects will already exist.

Field Datatypes and Sizes

The following table lists the available data types, storage requirements, and the keywords for creating the field type in a SQL CREATE TABLE statement.

Data Type	Keyword	Storage Size (bytes)	Description
Binary	BINARY	1	For queries on attached tables from database products that define a Binary data type.
Boolean	BIT	1	True or False values. Note that although the data type is BIT, the storage size is one byte. Additionally, BIT values cannot be Null in a Jet database.
1 Byte Integer	BYTE	1	An integer value between 0 and 255.
Autoincrement Field	COUNTER	4	A number automatically incremented by the Jet engine whenever a new record is added to a table. In the Jet engine, the data type for this value is a Long.
Financial Values	CURRENCY	8	A scaled integer between -922,337,203,685,477.5808 and 922,337,203,685,477.5807. You should always use the CURRENCY data type when dealing with money for greater accuracy in doing financial calculations. (Don't use floating point data.)
Dates	DATETIME	8	A date or time value between the years 100 and 9999. Keep in mind that the current calendar hasn't been in use forever, so don't assume you can do legitimate date math going back to the year 100.
Single Precision Floating Point	SINGLE	4	A single-precision floating-point value with a range of -3.402823E38 to -1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values, and 0.
Double Precision Floating Point	DOUBLE	8	A double-precision floating-point value with a range of -1.79769313486232E308 to -4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232E308 for positive values, and 0.
2 Byte Integer	SHORT	2	A short integer between -32,768 and 32,767.
4 Byte Integer	LONG	4	A long integer between -2,147,483,648 and 2,147,483,647. Note that this is also the data type of the counter field.
Text	LONGTEXT	1 byte per character	Used for long text strings up to a maximum of 1.2 gigabytes. (Don't use this data type if you can get by with the 255 character limit of the normal TEXT data type. The TEXT data type is significantly faster for Jet to retrieve.) This field type is called a Memo in Access.
Binary	LONGBINARY	As required.	Zero to a maximum of approximately 1 gigabyte. Used for OLE objects.
Text	TEXT	1 byte per character	Zero to 255 characters. You must specify the size of the field when creating a TEXT field, but the size you specify is a maximum allowable size. Jet always stores text data as variable length strings. (This data type offers much better performance than the LONGTEXT type. If you can fit the data in 255 characters or less, don't use LONGTEXT.)

Perhaps you noticed that this table is nearly identical to the help file. If you didn't, searh the VB help file for the topic "Microsoft Jet Database Engine SQL Data Types".

Creating Constraints

Constraints are used to create primary keys, unique indexes, and foreign keys. You can use a CONSTRAINT clause in both the field definition or at the table level for multi-column keys and indexes. If a constraint is being placed on a single column, you can define it within the column definition. Multiple column constraints are defined at the table level.

Single Column Constraint

Mulitple Column Constraint There are three types of constraints you can create:

• Primary Key
• Unique Index
• Foreign Key

We've already seen examples of creating both a single column and multi-column primary key. The syntax for creating a unique index is identical, except that with a unique index, there may be null values in the index (nulls are not allowed in primary key). To create a unique index, simply replace the keywords PRIMARY KEY with the keyword UNIQUE.

Let's take a look at creating a foreign key constraint by expanding on the previous definition of the BookAuthors table. The foreign key is specified by using the REFERENCES keyword. In this case, we are enforcing the BABookID key against the primary key of the Books table and enforcing the BAAuthorID key against the primary key of the Authors table.

Single Column Foreign Key Constraint

It's also possible to create a multiple column foreign key. Here we will reference the two column primary key of the BookAuthors table.

Multiple Column Foreign Key Constraint

One of the things I like about using SQL (or even DAO) to create relationships is that you can give the foreign key constraint a meaningful name. Although you may never need to refer to the name of the constraint in code, if you do its nice not to have to deal with a name like "Reference86" that Access creates when you use the Relationships window.

ALTER TABLE

Now that you understand the CREATE TABLE statement, using ALTER TABLE requires only the addition of a few keywords to modify an existing table. The general syntax of ALTER TABLE is: The syntax which follows ADD COLUMN or ADD CONSTRAINT is identical to that used for the CREATE TABLE statement. Its essentially the same as CREATE TABLE with the exception of the ADD keyword.

I hate to have to put it this way, but if you can't figure the rest of adding a field or constraint on your own, either reread the CREATE TABLE section, consult the help files, or give up in disgrace. :-)

Let's look at one of the previous examples and drop a column we created earlier: Now we'll drop a constraint: As you can see, SQL makes it almost dangerously easy to delete elements of a database structure. We'll look at some other things you can do with the DROP keyword later in the section on the DROP statement.

Just in case you didn't read the little note above, I'll be thorough in my examples and restore the column and constraint I just dropped.

CREATE INDEX

Creating an index on one or more columns using CREATE INDEX is similar to constructing a key using the CONSTRAINT clause with CREATE TABLE or ALTER TABLE, but the CREATE INDEX statement gives you a few additional options not available when using CREATE TABLE or ALTER TABLE.

Here's the general syntax:

Here's a few things you can do with CREATE INDEX that aren't available with the CONSTRAINT clause.

• Create an index which is not a constraint.
  A constraint resticts the data which can be entered in the table. You may (in fact should) create indexes strictly for performance reasons which are not constraints on the table data.
• Create a descending index.
  The CONSTRAINT clause creates only ascending indexes.
• Specify the handling of null values in the index.
  Using the WITH clause, you can specify if the index will not allow nulls or simply ignore nulls.

For the following examples, we'll use this sample table: First we'll create the primary key for the table: Next, we'll index the foreign key to the Companies table and disallow nulls so that the value will be required in the table. Since we'll often be sorting the data on the name, we'll create a multi-column index on the first, middle and last names. Since there may be records with null values in one or more of the columns, we'll ingore nulls in the index. Finally, we'll create a unique index on the social security number column. (This column would also be a candidate for a primary key since it must be unique for each entry.)

Database theory purists would probably take issue with the statement that the social security number is guaranteed to be unique. I'll leave that discussion to the newsgroups and chat rooms. For now, I'll only state that, IMHO, it's always best to use an arbitrary value for a primary key column.

A column such as this which is both a required entry and required to be unique, but not the primary key for the table, is sometimes referred to as an alternate key.

That's all there is to creating indexes on tables.

DROP Statement

The DROP statement is one of the easiest of SQL statements to use. Use DROP to delete tables or indexes on tables.

Here's the general syntax for using DROP:

It doesn't get any easier than this with SQL. Let's delete the Customers table we created earlier. Now lets drop the idxCustName index we created on the Customers table (imagine for the moment that we haven't already dropped the Customers table itself). As you can see, the DROP statement is exceedingly simple - almost dangerously simple. When using the DROP statement - especially with a production database - remember that there's no such thing as an "undelete" function.

---

You're now ready to begin creating databases with SQL. Once you get a handle on the syntax of a few simple statements, you'll find using Jet SQL to create database objects to be an easy and fast method of constructing a database.

---

Querying the Database with SQL

---

The SELECT statement is the key to querying data in your database. There are lots of variations, so we'll start by looking at the general syntax of a SELECT statement. Let's break down some of the components of the SELECT statement.

• Field List
• FROM Clause
• WHERE Clause
• GROUP BY Clause
• HAVING Clause
• ORDER BY Clause
• Other Options

Understanding the construction of a SELECT query is vital to being able to build a database application. Even if you use the Access query design window to build most of the queries in your application, you may still need to create a query "on-the-fly" in code. While you can "cheat" and use the Access query designer for most of the work, you'll still need to understand the fundamentals of the SELECT statement to build the SQL string.

Using the Access query designer isn't really cheating - I do it all the time. You can save yourself a considerable amount of development effort by using this tool. However, you need to understand that the query designer is really just a nice user interface for building a SELECT statement.

For most SELECT queries, the key areas you'll need to understand are the field list, FROM clause, and WHERE clause. The GROUP BY and HAVING clauses are used for totals queries, often with aggregate functions. Use the ORDER BY clause to sort the data in the query.

Field List

The field list is used to determine which fields in the database will be returned in the query results. The basic form of the field list in a SELECT query is as simple as entering the names of the fields you want included, separated by commas. There are several additional options that are available:

• The asterisk (*) wildcard.
  You can use SELECT * to retrieve all fields from all tables in the query, or use SELECT table.* to retrieve all fields from a single table.
  SELECT * FROM Customers
  or...
  SELECT CustID, CustCompanyID, Companies.*
  FROM Customers INNER JOIN Companies
  ON Customers.CustCompanyID = Companies.CompanyID
  Note: Use SELECT * with caution. Generally, you should specify only the fields you need for the best performance in your application. Don't make the Jet engine do more work than it needs to do.
• AS clause.
  You can alias the actual field names using AS alias. Additionally, if you are using an expression as a field, you must use an AS clause. Remember that if you alias a field to a name that includes spaces, you must enclose the name in square brackets ([ and ]).
  SELECT CustFirst AS [First Name] FROM Customers
• Expressions as fields.
  You can enter a valid expression as a column using aggregate functions or other simple expressions. Remember that you must alias the expression using AS.
  SELECT CustFirst & " " & CustLast AS [Customer Name] from Customers
• Table.Field Syntax.
  If two or more fields from all available tables in the query have the same name, you must qualify the field names with the table names. This only applies if you are joining tables in the query.
  SELECT CustID, Customers.CompanyID, CompanyName
  FROM Customers INNER JOIN Companies ON
  Customers.CompanyID = Companies.CompanyID

FROM Clause

The FROM clause is used to choose the tables from which you will select fields. You can use only a single table or include multiple tables by using joins. Since you've already seen examples of using a single table FROM clause, we'll focus here on creating joins. You can also specify that a table from an external database be included using an IN clause.

You can create inner joins using the where clause of a SQL statement, but you must use the RIGHT JOIN or LEFT JOIN syntax to create outer joins.

Joining Tables

Joins demonstrate the real power of a relational database. By joining two tables, you can create a flat (two-dimensional) view of a one-to-many or many-to-many relationship. The Jet engine supports both inner joins and outer joins. In an inner join, only the matching records from both sides of the join are included in the results. In an outer join (either a LEFT JOIN or a RIGHT JOIN), the results include all records from one table and only the matching records from the other.

Inner Joins

You create an inner join in the FROM clause of a SELECT query using the INNER JOIN keywords. The general syntax for an inner join FROM clause is:

table1 INNER JOIN table2 ON table1.field = table2.field

Jet requires that you fully qualify the field names in the ON clause of inner and outer joins even if both field names are unique in the query. If you don't use the full table.field form, you'll get a syntax error from Jet.

With inner joins only, you can also use the WHERE clause of a SELECT query to create a join, although the Jet documentation recommends using the INNER JOIN syntax. Here's what an inner join looks like when using the WHERE clause:

Oddly enough, you do not need to use the fully qualified field names when creating a join in a where clause. Additionally, if you should happen to forget the WHERE clause with this syntax, you'll get a Cartesian product. If the tables are large, the query could run endlessly.

Outer Joins

Outer joins are created using either the LEFT JOIN or RIGHT JOIN syntax. Don't let the difference between the two confuse you. It's easy to remember what records will be returned after you type the statement. With a left join, the table that appears to the left of the LEFT JOIN keywords will return all records. With a right join, the table on the right will return all records. Let's look at examples of each.

Return all records from Companies and only matching records from Employees:

Certain types of outer joins cannot be resolved by Jet. The following diagram illustrates two outer join constructions that will fail:

Outer Join Constructions

There may be other outer joins which are not supported, but these are the only ones I've encountered. Don't feel too frustrated by this limitation, however - Jet is much more flexible with outer joins than most database engines.

IN Clause

Using the IN clause, you can specify a table from an external database. Note that this does not include attached tables. If the database is a Jet database, the syntax is as simple as specifying the file name: Several other database formats are supported, including dBase, Paradox, and Btrieve. For the syntax, search the VB help files for the keywords "IN clause".

If you're using Access, you can make data from tables available without having the tables appear in the database window by creating a query and using an IN clause to specify another database file. However, if you do this you should keep in mind the fact that you're distributing the path to the external database throughout your SQL instead of consolidating it in the Connect property of attached tables. In a large database, this could be a maintenance nightmare.

WHERE Clause

The WHERE clause in a query determines which records will be returned from the tables included in the FROM clause. The general format of a WHERE clause is:

Note: A WHERE clause can contain up to 40 expressions linked by logical operators, such as And and Or.

Let's look at each of the components:

• Field
  Any valid field in the SELECT statement, even a calculated field (although you must use the HAVING clause if the expression is an aggregate function).
• Comparison Operator
  Any valid SQL comparison operator, such as =, >, <, etc. You can also use BETWEEN ... AND for ranges and the LIKE operator to compare the field to an expression containing wildcards. If you are testing for a null, you must use IS NULL or IS NOT NULL.
• Value
  The value you are comparing to. The key to expressing the value component correctly is to be aware of the datatype of the field. (See below.)

For normal fields (not calculated fields based on expressions), there are three basic scenarios for writing a WHERE clause expression: numeric, text, and date. Remember, the key is to correctly match the syntax of the expression to the data type of the underlying field (not of the value in the test). Each of these three basic comparisons requires that different delimiters be used for the comparison value. There are also two special cases: Booleans and Nulls.

Data Types in WHERE Expressions

Data Type	Jet Field Data Types	Delimiter	Example
Numeric	Long Integer Integer Byte Single Double Currency	No delimiter	CustID = 1
Text	Text Memo	Quoted	CustLast = 'Garrick' Note: Jet allows the use of either single or double quotes. For the best compatibility with other database engines, you should stick with using single quotes. Also note that if the value contains a single quote, you need to replace it with two single quotes. (You may wish to write a simple procedure to replace all instances of a single quote in a string with two single quotes.)
Date	DateTime	Pound Sign (#)	OrdDate = #1/1/1980# Note: If you're planning to eventually upsize a Jet database to SQL Server, date comparisons in SQL strings may be a problem since SQL Server uses single quotes as date delimiters. Here again, a small procedure that builds the appropriate string may be useful. Also note that regardless of your international settings, you must always use the U.S. date formats mm/dd/yy or mm/dd/yyyy.
Boolean	Yes/No Any integer type	N/A, use the True keyword	CustIsApproved = True Note: You can use true with any numeric field or a Yes/No (Boolean) field. Like Visual Basic in general, Jet treats any non-zero value as True and zero as False. If you look at a Yes/No column in an Access datasheet, you'll see that, also like VB, Jet stores -1 for True. Don't, however, compare to -1, use the True keyword.
Nulls	All	N/A, use IS NULL	CustAddress IS NULL Note: See below under Comparison Operators for more on Nulls

Comparison Operators

Jet provides the following standard comparison operators:

• =
  Values are equal.
• <
  Field is less than value.
• >
  Field is greater than value.
• <=
  Field is less than or equal to value.
• >=
  Field is greater than or equal to value.
• <>
  Field is not equal to value.

There are also four special comparisons available: BETWEEN...AND, LIKE, IS NULL, and IS NOT NULL.

• BETWEEN...AND
  This is equivalent to Field >= Lowerbound And Field <= Upperbound.
• LIKE
  LIKE is used to make pattern matching comparisons with or without wildcards. In Jet SQL, the asterisk (*) represents many characters and the question mark (?) represents a single character. There are a variety of other pattern matching methods available. For a complete description of the use of the LIKE operator, search the Visual Basic help files.
  Note: Why the authors of Jet used these wildcard characters instead of the pound sign (#) and underscore (_) used by every other SQL dialect I've ever seen is beyond me, but there it is.
• IS NULL
  Use IS NULL to determine if the field contains data.
• IS NOT NULL
  Use IS NOT NULL to determine if the field does not contain data.

GROUP BY Clause

A GROUP BY clause in a SELECT query is used to combine individual records into a single record and, if used with an aggregate function such as Sum or Count, will generate a summary value for each grouping.

If, for example, you have a table of orders, and you want to get the total number of orders for each particular item, a SELECT query with a GROUP BY clause might look like this:

This query will generate one record for each unique value in the OrdItem column. The TotalOrders column in the query will show the number of records in the table for each different value in the OrdItem column.

HAVING Clause

A HAVING clause specifies which records are included in a SELECT query with a GROUP BY clause. While similar to a WHERE clause, the HAVING clause can be used to place criteria on aggregates in the query results. A HAVING clause is used only with a GROUP BY clause.

Continuing with the previous example, the HAVING clause can be used to restrict the results in a manner different from what can be done with a WHERE clause. If the list of possible values in the OrdItem column are Wheel, Tire, Axle, Bearing, and Spoke, you could use a WHERE clause to restrict the query to a subset of all possible values:

A HAVING clause, on the other hand, could restrict the results to only those items with over a specific number of orders: This would return one record for each of the available values in OrdItems only if there were more than 100 orders for that item.

ORDER BY Clause

The ORDER BY clause is used to specify the sorting of records in a SELECT query. The general form of the ORDER BY clause is: You can specify more than one field. If there are multiple fields specified, the results will be sorted by the first field listed, then by the second field in cases where the values in the first field are equal, and so on. By default, records are sorted in ascending order (a-z, 0-9), but you can explicitly specify an ascending or descending sort with the ASC or DESC keywords.

Remember that if no ORDER BY clause is present, records are unsorted. Normally, they will appear in the order they are entered, but you cannot rely on a specific sort order unless you include an ORDER BY clause.

The ORDER BY clause is normally the last clause in a SELECT query.

Other Options

There are two additional options available with SELECT queries: the predicate to the SELECT keyword and the WITH clause.

Predicate

The predicate is used to restrict the records returned by the SELECT statement and has the following options:

ALL	The ALL predicate returns all records that meet the conditions in the SELECT statement. It is not necessary to specify ALL in a SELECT statement because ALL is assumed if no predicate is specified.
DISTINCT	DISTINCT omits records that have duplicate data in the selected fields. For example, the following SELECT statement:     SELECT DISTINCT CustLast FROM Customers;                 ...will return one record for each unique value in the CustLast field. The behavior of the DISTINCT predicate is similar to using a GROUP BY clause. Note: Using DISTINCT will make the results of the query non-updatable.
DISTINCTROW	The DISTINCTROW predicate omits data based on entire duplicate records, not just duplicate fields. DISTINCTROW is useful only when the SELECT query includes more than one table, but you are only returning fields from one table. If only one table is included in the query, or you include fields from all tables, DISTINCTROW is not useful.     SELECT DISTINCTROW CustName     FROM Customers INNER JOIN Orders ON Customers.CustID = Orders.OrdCustID     WHERE OrdTotal > 100     ORDER BY CustName;                 This query will return a unique list of the customers who have orders totaling over $100.00. Since each customer can have many orders, the DISTINCTROW keyword eliminates the duplicate values. Note: Using DISTINCTROW will make the results of the query non-updatable.
TOP	The TOP keyword is used to restrict the results to a fixed number of records or a percentage of the total qualifying records in the results. For, example, to return the top 10 states by population, you would use:     SELECT TOP 10 StateName FROM States ORDER BY StatePopulation;                 Continuing with the same example, you could also return the top 25% of states by population:     SELECT TOP 25 PERCENT StateName FROM States ORDER BY StatePopulation;                 While there is no BOTTOM keyword, you can achieve the same result by changing the ORDER BY clause:     SELECT TOP 25 PERCENT StateName FROM States ORDER BY StatePopulation DESC;                 Note: Using TOP doesn't affect the updatability of the results of a SELECT query.

WITH clause

With Jet SQL, the only option available in the WITH clause is WITH OWNERACCESS OPTION. This clause can be used in a secured environment to allow users to select data in the context of another user account. If WITH OWNERACCESS OPTION is included, the query will be run using the owner's permissions on the underlying tables, rather than the user's permissions. By using this clause, you can do an "end-run" around the security settings for a table. It can be particularly helpful if you wish to define a view of a table where the list of rows or columns is restricted to a subset of the data.

If, for example, you have a table of employee information that includes name, phone, and salary information, you may wish to restrict access to the table so that only managers can see the salary data. Using the security system, you can restrict the read data permission to only the managers. However, you may wish to allow all users to see the name and phone information. By using the WITH OWNERACCESS OPTION clause, you can allow all users to see a subset of the data by building a query as follows:

Anyone with read data permission on this query will be able to see the name and phone but not the salary, regardless of their underlying permission on the Employees table.

WITH OWNERACCESS OPTION is only useful when working with a saved query definition (as opposed to a dynamically generated SQL statement) in a secured, multiuser environment.

---

If you don't learn anything else about SQL, learn how to build a SELECT statement. Not only is SELECT the most commonly used of the SQL statements, but many of the components of the SELECT statement are used in other SQL statements. You may know the DAO hierarchy as well as the back of your own hand, but you'll be unable to do anything useful in database programming if you can't construct a SELECT statement.

---

Managing Data with SQL

---

There are three basic operations you can do with a record in a table: insert, update, and delete. Not surprisingly, SQL provides the INSERT, UPDATE, and DELETE statements to perform these tasks. Additionally, SQL provides a fourth possibility with SELECT ... INTO, which can be used to create a new table from an existing table.

Note: Access adds a little confusion to these SQL operations by referring to an INSERT statement as an Append query and a SELECT INTO statement as a Make-Table query. Maybe its just me, but I don't find the word "append" any more obvious than "insert". At least they left update and delete intact. All of these query types are known collectively as action queries.

All of the action queries are run in code using the Execute method of a database object. If you're building database applications using the data control, you probably won't be using these query types often. However, if you're using unbound data, INSERT, UPDATE, and DELETE queries will be as common as SELECT queries in your application.

• INSERT Statement
• UPDATE Statement
• DELETE Statement
• SELECT...INTO

INSERT Statement

The INSERT statement is used to insert new records in a table. There are two basic forms of the INSERT statement:

Insert records into a destination table from a source table (or tables):

Insert a new record based on fixed values:

Copying Records

The first form of the INSERT statement - INSERT ... SELECT - is essentially a record copying operation. Records from the table (or query) indicated by the SELECT portion of the statement are appended to the records specified by the destination table. You can also use the IN clause to send the records to an external database. If you wish to add a field that doesn't exist in any table to the query, you can use an AS clause in the SELECT statement to generate a calculated field.

Let's say we have two tables, Customers and Prospects which have identical field lists. Instead of working with two tables we decide to combine them into a single table with a Type field that identifies the type of person as a customer or a prospect. Here's the SQL statement:

Note: The Visual Basic help file topic for the INSERT INTO statement incorrectly states that if the table you are copying from has a counter field that you should not include the counter in the query. The fact is that you can include the counter field if the values don't violate the primary key in the destination table.

You may want to do this in a situation where you have a relationship defined with the source table and you would like to preserve the links to records in other tables. Here's one way to do this (this assumes that there are values in the source table that would violate the foreign key in the destination table and that both keys are counter fields - note also that this will not necessarily work if you're using random instead of incrementing counters with Jet 3.x):

1. Open the source table and change the data type of the counter field to Long Integer.
2. Using an UPDATE query, add an arbitrary value to the counter field so that the updated keys will no longer violate the primary key in the destination table. If the destination table has a counter field who's largest value is 4000, you could simply add 5000 to each counter value in the source table. In our example, the UPDATE query would be:
   UPDATE Customers SET CustID = CustID + 5000;

Once you've solved the key violation problem, you're ready to run the INSERT INTO statement.

Inserting a Single Record

The second form of INSERT INTO can be used to append a single record to a table using the VALUES clause of the INSERT INTO statement. For example: Using an INSERT INTO statement with the Execute method of the Database object is probably the fastest method of adding a record to a table, but there are two problems with it:

1. In comparison to using DAO methods for a single record insert, building the SQL statement can get rather complicated - although it is a fairly straightforward coding task.
2. If you are using a counter as the primary key for the table, there is no means of obtaining the new counter value.

Because of these problems - the second item in particular - INSERT INTO is not often used for adding records to tables that have counters as a primary key. If, however, you have a table where the primary key value is known before the record is inserted or if you don't need the new key value then INSERT INTO is a very fast and efficient means of adding a record.

UPDATE Statement

The UPDATE statement is used to modify one or more records in a table. The general form is: The UPDATE statement will modify all records matching the criteria specified by the WHERE clause.

Note: It's particularly important to make sure you get the WHERE clause correct in an UPDATE statement. If, for example, you accidentally omitted the WHERE clause you would modify all of the records in the table.

There's no difference in the syntax for an UPDATE statement that modifies one record or several records - the WHERE clause controls which records are modified. Let's look at an example. Here we will "upgrade" the skill level of some programmers:

Here we'll update a single record (assume that PgmrID is the primary key):

Here we'll change all of the newbies to intermediates: And finally here we'll just make them all experts: You can also update multiple fields in a single UPDATE statement. Let's upgrade programmer 10 again: This also works with bulk updates: The UPDATE statement is a very efficient method of modifying data in a table. Since you already know how to restrict the records via the WHERE clause, you don't have the problems of determining a primary key that you have with the INSERT INTO statement. In most cases, an UPDATE statement should provide much better performance than using a Recordset object and the Edit and Update methods.

DELETE Statement

The DELETE statement removes entire records from a table. The general form is: The first part, table.*, is optional. If you are deleting records based on a selection that does not include joins, you can simply specify: The FROM and WHERE clauses are identical to those you would build for a SELECT statement. Let's delete some of the programmers we modified above: When used with a multiple table expression, you can base the criteria on fields from a table other than the one where the records will be deleted. Here we have deleted all programmers associated with project 10.

There are a few additional things to keep in mind when using a DELETE statement:

• There is no "undelete" function, so use DELETE with caution.
• If you want to remove all the records from a table, it may be more efficient to simply delete the table using a DROP TABLE statement. However, if you use a DELETE statement, the table structure is left intact. To delete all records from a table, just omit the WHERE clause.
• Deleting records from the one side of a one to many relationship will also delete records from the many side if the relationship was established with cascading deletes.

Like the UPDATE statement, DELETE is a highly efficient means of removing records from a table and in most cases will be faster than the equivalent Delete method of a Recordset object. The only problem that using a DELETE statement can present is if you delete the current record from a form, you will need to build logic to determine what to display in place of the deleted record. Since you are not working with a Recordset, you don't have the MoveNext or MovePrevious methods available to simulate the behavior of the data control or a normal Access form.

SELECT...INTO Statement

The SELECT...INTO statement statement creates a new table from an existing table or query. Here's the general form: By this point, you should already have a pretty good idea what the components of the this SQL statement do. Following the keyword SELECT is a field list. After INTO comes the name of the new table to be created. The IN clause can optionally be used to specify a database other than the current database as the destination. The FROM clause is a standard SQL FROM clause and can include joins, criteria, and so on.

Here is a simple example that will make a new table from a subset of a Customers table:

There are a few points to keep in mind when using SELECT...INTO:

• When you create a new table using SELECT...INTO, the new table inherits the field data types and sizes of the source tables, but no other field or table properties are transferred. This includes indexes, rules, relationships, and so on.
• To add data to an existing table, use INSERT INTO rather than SELECT...INTO.
• You can use the ALTER TABLE statement to recreate any indexes, etc., that were lost in creating the new table.

The SELECT...INTO statement can be very convenient and powerful, especially when it's time to start running a series of reports. Rather than base a set of reports on a complex multi-table join, you can use a single SELECT...INTO to create a new table based on the complex query, then base the reports on the new table. By using ALTER TABLE to add indexing for fields used as criteria or for sorting, you can significantly improve the speed of reports. If you don't want to deal with constantly changing names for the sources of reports, you can also use the DROP TABLE statement to remove an old copy of the temporary table - then recycle the old name. Keep in mind that if you do this, you will need to execute the DROP TABLE statement before executing the SELECT...INTO. SELECT...INTO will fail if the destination table already exists.

---

You are now ready to leave nearly everything in DAO behind you. Using the Execute method of the Database object, you can perform nearly any data management task that might come your way. In the next section, we'll take a look at a few things that you can't do with Jet SQL. There isn't much in the list, but some of the items are important tasks.

---

Limitations of Jet SQL

---

Don't be fooled into thinking that you can completely ignore DAO. There are some things that aren't available with Jet SQL.

• Rules and Defaults
  While relationships can be established with SQL, you'll need to use DAO to assign some of the extended properties for tables and fields.
• Creating a Database
  There's no Jet equivalent to the SQL CREATE DATABASE statement. You'll need to use DAO to create a new database.
• Manage Security
  Jet SQL does not provide the GRANT and REVOKE statements. Use DAO to manage security.
• Non-Jet Databases
  Jet SQL can only create Jet objects. You need to use DAO to create objects in other databases.
• Creating Queries
  Jet SQL has no CREATE VIEW statement. Use the CreateQueryDef method of the database object and set the SQL property to a valid SQL statement.
• Other Access Objects
  SQL cannot create any of the other Access objects, such as Forms, Reports, Macros, Modules, etc. However, these can be created created programmatically in Access using some of the extended methods available in Access, such as CreateForm, CreateReport, etc. Using some of the macro actions available in code, you can even create a module and write code for it programmatically.

---

Summary

---

Jet SQL provides a fast and efficient means of managing both the structure and the data in a database. While creating some of the SQL statements in code can be complex, it is no more difficult than navigating through the often cumbersome DAO hierarchy. In many cases, executing a SQL statement using the Execute method of the Database object is faster than the equivalent DAO operation and in some cases (perhaps most) it can be simpler to code.

Let's review some of the key points:

• Use SQL Data Definition Language to manipulate the structure of a database. The most commonly used statements are CREATE TABLE, ALTER TABLE, and DROP.
• Use the SELECT statement to retrieve data in queries.
• SQL can change data as well as retrieve it. Use INSERT INTO, UPDATE, DELETE, and SELECT...INTO to create, change, and remove records.
• Jet SQL can't do everything so you'll still need to know your way around the DAO hierarchy to perform some tasks. Notable omissions from Jet SQL are building queries and managing security.
• Use the Execute method of the DAO Database object to execute a SQL statement. In most cases you will want to use the dbFailOnError option so that errors in the execution of the SQL statement can be trapped.

---

Originally written by Joe Garrick