A More Powerful SQL

The Jet 4.0 ANSI SQL-92 Extensions

By Paul Litwin, Ken Getz, and Mike Gilbert

Microsoft Access has always been a leader when it comes to querying. Since the beginning days of Access 1.0, you could always construct a query in Access using either the Query By Example-based query designer or Structured Query Language (SQL). The fact that Access lets you toggle between QBE and SQL views has helped many a developer learn SQL.

Unfortunately, Access' version of SQL (or more correctly, the Jet Engine's version) is a unique dialect of SQL. Just pinning down Access' level of conformance is a chore; although Jet 3.5 SQL supports only a subset of SQL-89, it supports some elements of the newer ANSI SQL-92 standard. This fact was not lost on the Access 2000 and Jet 4.0 development teams, who have made several changes that significantly expand the Jet Engine's support for the ANSI SQL-92 standard.

But there's a catch: You can only take advantage of the majority of these new ANSI SQL-92 extensions when using Jet databases, ADO, and the Jet OLE DB provider. In case you're wondering, the Jet OLE DB provider is automatically used when you create an ADO Connection object in an Access module and set it to point to CurrentProject.Connection.

If you're reading between the lines, you'll notice we said "the majority." Though the Microsoft documentation states the extensions are only available when using ADO, a little testing confirms that some of the extensions are indeed available from the Access UI. For example, many - but not all - of the extensions to the ALTER TABLE and CREATE TABLE statements are available from the Access UI.

By the way, you can't use the new ANSI SQL-92 extensions with Access projects (ADP files) or the Microsoft Data Engine (MSDE). ADP files and MSDE use SQL Server's dialect of SQL, known as Transact-SQL or T-SQL, which conforms more closely to the ANSI SQL-92 standard than Jet SQL - even with the Jet ANSI SQL-92 extensions.

In this article we'll discuss the ANSI SQL-92 extensions, highlighting the differences between standard Access SQL and the new Jet 4.0 extensions. All the extensions are in the Data Definition Language (DDL) area of Jet/Access SQL. You use DDL SQL commands to change the schema of a database. The other major area of SQL, Data Manipulation Language (DML), is not affected by the extensions. The table in FIGURE 1 provides an overview of the Jet 4.0 ANSI SQL-92 extensions.

FIGURE 1: Summary of Jet 4.0 ANSI SQL-92 extensions.

The CREATE TABLE Statement

The Jet 4.0 ANSI SQL-92 extensions add a number of new features to the CREATE TABLE statement. The extensions add support for defaults, check constraints, cascading referential integrity, fast foreign keys, and the ability to alter the AutoNumber seed and increment values.

Default. Before Access 2000, there was no way to specify a default for a column in a CREATE TABLE statement. However, using the new ANSI SQL-92 extensions, you can now use the following syntax to create a default for a column:

DEFAULT ( value ) 

For example, the following CREATE TABLE statement sets a default of 0 for the QOH column:

CREATE TABLE tblInventory (ItemId LONG CONSTRAINT

PrimaryKey PRIMARY KEY, QOH LONG DEFAULT 0); 

Check constraint. Check constraints allow you to create business rules for a table. Check constraints serve the same purpose as Access column and table-level validation rules, but they're even more powerful because they can span multiple tables. In fact, you can use check constraints to perform much of the functionality of triggers - a feature that many high-end database servers, including SQL Server, support. Another advantage of check constraints is that they allow you to create multiple table-level validation rules, which is something the Access UI doesn't allow. On the other hand, unlike validation rules you can create via the Access UI, you can't specify a custom error message for a check constraint.

Here's the basic syntax for the Jet 4.0 check constraint:

[CONSTRAINT [name]] CHECK ( search_condition ) 

If you don't include a name for a constraint, Jet assigns one for you. Constraint names must be unique across the database.

For example, say you wished to create the tblInventory table with a check constraint that limits the QOH column to values between 0 and 10,000. You could use the following CREATE TABLE statement:

CREATE TABLE tblInventory (

  ItemId LONG CONSTRAINT PrimaryKey PRIMARY KEY, 
      

  QOH LONG, 

  CONSTRAINT QOHLimit CHECK (QOH BETWEEN 0 AND 
      10000)); 

The following CREATE TABLE statement includes a check constraint that ensures the Quantity column is less than or equal to the value of QOH in tblInventory for the item:

CREATE TABLE tblNewOrder1 (

  OrderId AUTOINCREMENT, ItemId LONG, Quantity 
      LONG, 

  CONSTRAINT QuantityOnHand CHECK (Quantity <= 
      (

    SELECT QOH

      FROM tblInventory 

     WHERE ItemId = 
      tblNewOrder1.ItemId))) 

Cascading referential rntegrity. The Jet 4.0 ANSI SQL-92 extensions add support for the creation of cascading referential integrity constraints. This feature has been supported by the Access UI for some time, but not via SQL DDL.

The syntax for the enhanced foreign key constraint is:

CONSTRAINT name FOREIGN KEY (column1 [, 
      column2 [, ...]]) 

REFERENCES foreign-table [(foreign-column1 
      [,

foreign-column2 [,...]])] [ON UPDATE {NO ACTION | 
      CASCADE}]

[ON DELETE {NO ACTION | CASCADE}]

If you specify ON UPDATE NO ACTION, or don't include the ON UPDATE keyword, you won't be able to change the value of a primary key if it's referenced by one or more records in the foreign table. If you specify CASCADE, however, the updated primary key value will be cascaded to any referenced records in the foreign table.

If you specify ON DELETE NO ACTION, or don't include the ON DELETE keyword, you won't be able to delete a record from the primary table if it's referenced by one or more records in the foreign table. If you specify CASCADE, however, the referenced records in the foreign table will also be deleted. For example, the foreign key created in the following CREATE TABLE statement will cascade deletions and updates:

CREATE TABLE tblNewItems2 (

  OrderId LONG, ItemId LONG, ItemDescription TEXT 
      (30), 

  CONSTRAINT PrimaryKey PRIMARY KEY (OrderId, 
      ItemId), 

  CONSTRAINT tblNewOrder1FK FOREIGN KEY (ItemId) 
      REFERENCES

  tblNewOrder1 ON UPDATE CASCADE ON DELETE 
      CASCADE); 

Fast Foreign Keys

Jet normally indexes all foreign key columns, which is usually a good thing. However, these automatically-created indexes can adversely affect performance and concurrency when the foreign key columns contain a large number of duplicated values. For example, a column such as gender, country, or city might contain a large number of duplicates. In these cases, you may wish to tell Jet to create a fast foreign key, i.e. a key without an accompanying index.

The syntax for creating a fast foreign key is as follows:

CONSTRAINT name FOREIGN KEY NO INDEX 
      (column1

[, column2 [, ...]]) REFERENCES 
      foreign-table

[(foreign-column1 [, foreign-column2 [, 
      ...]])] 

[ON UPDATE {NO ACTION | CASCADE}]

[ON DELETE {NO ACTION | CASCADE}]

If you leave out the NO INDEX keywords, of course, Jet creates a normal foreign key column, i.e. one with an index.

In the following example, we create two tables: tblGender and tblCustomer2. The second table, tblCustomer2, contains a fast foreign key for the gender column:

CREATE TABLE tblGender (

  Gender TEXT (1) CONSTRAINT PrimaryKey PRIMARY 
      KEY); 

CREATE TABLE tblCustomer2 (

  CustomerId INTEGER CONSTRAINT PrimaryKey PRIMARY 
      KEY, 

  FirstName TEXT (50), LastName TEXT (50), Gender 
      TEXT (1), 

  CONSTRAINT tblGenderFK FOREIGN KEY NO INDEX 
      (Gender) 

  REFERENCES tblGender); 

Jet 4.0 SQL provides the only way to create fast foreign keys.

AutoNumber enhancements. The Jet 4.0 ANSI SQL-92 extensions add support for customizing the seed and increment values of AutoNumber columns. The syntax is as follows:

column AUTOINCREMENT (seed, 
      increment)

You can also use the synonyms IDENTITY or COUNTER instead of AUTOINCREMENT. Jet 4.0 SQL provides the only way to alter the seed and increment values of AutoNumber fields. The Access UI and ADOX provide no mechanism for customizing AutoNumber seed or increment values. For example, to create the OrderId AutoNumber column with a sequence that started at 1000 and was incremented by 10, you could use the following CREATE TABLE statement:

CREATE TABLE tblNewOrder2 (

  OrderId AUTOINCREMENT (1000, 10), 

  ItemId LONG, Quantity LONG) 

You can also modify the seed and increment value for an existing AutoNumber column using the ALTER TABLE statement:

ALTER TABLE tblOrder 

  ALTER COLUMN OrderId COUNTER (2000, 50) 

Please realize that Jet won't prevent you from altering AutoNumber seed and increment values that produce duplicate values. However, if the AutoNumber column has been designated as the primary key or it contains a unique index, you will be prevented from saving rows with duplicates.

The Jet 4.0 extensions also add support for querying for the last-assigned AutoNumber value using the same syntax as SQL Server uses:

SELECT @@IDENTITY 

The ADO code from basAutoIncTest shown in FIGURE 2 illustrates how you might use the @@IDENTITY system variable.

Private Sub AutoIncTest()

  Dim cnn As ADODB.Connection

   Dim cmd As ADODB.Command

   Dim rst As ADODB.Recordset

   Set cnn = 
      CurrentProject.Connection

   Set cmd = New ADODB.Command

   Set 
      cmd.ActiveConnection = cnn

  cmd.CommandType = adCmdText

  cmd.CommandText = "INSERT INTO tblNewOrder2 " 
      & _

                    "(ItemId, 
      Quantity) VALUES (1, 20)"

  cmd.Execute

   Set cmd = Nothing

   Set rst = New ADODB.Recordset

  rst.Open "SELECT @@IDENTITY AS LastOrderId", 
      _

           cnn, Options:=adCmdText

   Debug.Print "OrderId for new record = " & _

  rst("LastOrderId") 

  rst.Close

   Set rst = Nothing

End Sub

FIGURE 2: ADO code from basAutoIncTest illustrating the use of the @@IDENTITY system variable.

The ALTER TABLE Statement

The Jet 4.0 ALTER TABLE statement has been extended to include support for the ALTER COLUMN clause. In the past, there was no programmatic support for modifying the definition of a column, which meant that to change a column's definition was an arduous process of creating a new table, copying the old records into the new table, deleting the old table, and renaming the new table. Support for ALTER COLUMN makes a column change much simpler. There are three forms of the ALTER COLUMN clause. The first form is used to change the definition of a column:

ALTER TABLE table ALTER [COLUMN] column 
      datatype [(size)] 

[DEFAULT default-value]

[CONSTRAINT single-column-constraint];

To change the data type of the ItemId column in the tblNewItems table from long integer to integer (also known as "short"), you could use the following statement:

ALTER TABLE tblNewItems ALTER COLUMN ItemId SHORT; 

The second form of the ALTER COLUMN clause is used to add a default for an existing column:

ALTER TABLE table ALTER [COLUMN] column 

SET DEFAULT default-value;

To add a default of 1001 to the column definition of ItemId, you could use this statement:

ALTER TABLE tblNewItems ALTER ItemId SET DEFAULT "1001"; 

The third form is used to remove a default for a column:

ALTER TABLE table ALTER [COLUMN] column DROP 
      DEFAULT; 

To drop the default for ItemId, you might use the following statement:

ALTER TABLE tblNewItems ALTER COLUMN ItemId DROP DEFAULT; 

Jet 4.0 View and Stored Procedure Extensions

To make Jet more compatible with SQL Server, Microsoft has added support for views and stored procedures. Jet 4.0's views and stored procedures aren't new Jet objects; they're Jet queries repackaged to work like ANSI SQL-92 views and SQL Server stored procedures. Views and stored procedures are stored as queries, but these special queries are not visible in the Access database container.

You use the CREATE VIEW statement to create a view using the following syntax:

CREATE VIEW view [(field1 [, field2 [, 
      ...] ])] 

AS select-statement;

The field names are optional; if you include them, Jet uses these names in lieu of the names of the fields from the underlying tables. A view is similar to a saved Access select query. You can use just about any SELECT statement in the view definition; however, don't include an ORDER BY clause. The following CREATE VIEW statement creates a view that joins the tblOrder and tblCustomer tables:

CREATE VIEW qryOrderCustomer AS

SELECT OrderId, LastName & ", " & FirstName AS 
      Customer, 

       OrderDate

  FROM tblOrder INNER JOIN tblCustomer

    ON tblOrder.CustomerId = 
      tblCustomer.CustomerId

Once created, you can use the view in the same places you can use an Access saved query. To drop a view, you use the DROP VIEW statement using the following syntax:

DROP VIEW view;

For example:

DROP VIEW qryOrderCustomer; 

You use the CREATE PROCEDURE statement to create a stored procedure using the following syntax:

CREATE PROC[EDURE] procedure

[(param1 datatype1 [, param2 
      datatype2 [, ...] ])] 

AS sql-statement;

The SQL statement can be just about any SELECT, action query, or DDL SQL statement. You use the EXECUTE statement to execute a stored procedure using the following syntax:

EXEC[UTE] procedure

[param1 [, param2 [, ...] ]] ; 

For example, you might create the following stored procedure that returns a single record from tblMenu:

CREATE PROCEDURE procGetMenu (lngItem LONG) AS

SELECT *

  FROM tblMenu

 WHERE MenuId = lngItem

You could then execute procGetMenu using the following EXECUTE statement:

EXECUTE procGetMenu 5

The following CREATE PROCEDURE statement inserts a record into tblMenu using an INSERT INTO statement:

CREATE PROC procAddMenu (lngId LONG, 

  strDescription TEXT, strUnits TEXT, 

  curPrice CURRENCY, blnDiscontinued BIT) AS

INSERT INTO tblMenu

VALUES (lngId, strDescription, strUnits, 

        curPrice, 
      blnDiscontinued); 

The following EXECUTE statement adds a row to tblMenu using the procAddMenu stored procedure:

EXECUTE procAddMenu 25, "Cherry Pie", "Slice", 3.50, False; 

To drop a stored procedure, you use the DROP PROCEDURE statement using the following syntax:

DROP PROC[EDURE] procedure;

For example:

DROP PROC procAddMenu; 

Jet 4.0 Transaction Extensions

The Jet 4.0 ANSI SQL-92 extensions add support for managing transactions using SQL. This support doesn't replace the transaction support already supplied by ADO, it merely serves an alternative syntax for managing transactions. A transaction may span multiple operations over a single ADO connection. To start a transaction, use the following syntax:

BEGIN TRANSACTION

To commit a transaction, use the following syntax:

COMMIT [TRANSACTION] 

To cancel a transaction, use the following syntax:

ROLLBACK [TRANSACTION] 

The subroutine from basTestSQLTrans illustrates how you might use the SQL transaction support (see FIGURE 3).

Public Sub TestSQLTrans(fCommit As Boolean)

   Dim cnn As ADODB.Connection

   Dim cmd As ADODB.Command

   Set cnn = 
      CurrentProject.Connection

   Set cmd = New ADODB.Command

   Set 
      cmd.ActiveConnection = cnn

  cmd.CommandType = adCmdText

  cmd.CommandText = "BEGIN TRANSACTION" 

  cmd.Execute

  cmd.CommandText = "DELETE FROM tblMenu WHERE " 
      & _

                    "MenuId > 5"

  cmd.Execute

   If fCommit Then

     cmd.CommandText = "COMMIT" 

   Else

    cmd.CommandText = "ROLLBACK" 

   End If

  cmd.Execute

   Set cmd = Nothing

End Sub

FIGURE 3: The basTestSQLTrans subroutine illustrating the use of the SQL transaction support.

Jet 4.0 Security Extensions

The Jet 4.0 ANSI SQL-92 security extensions provide a welcome alternative to the confusing security syntax employed by ADOX (and the older DAO). The Jet 4.0 security extensions support many of the Jet security features, although a few (for example, the ability to change object ownership) are not supported.

Creating accounts. You use the CREATE USER statement to create a new user account:

CREATE USER user1 password1 pid1

[, user2 password2 pid2 [, ...] ] 

User is the user name used to log into the account. Password is a modifiable password that is also required at login time. Pid is a personal identifier that makes an account unique across security workgroups. Notice that you separate the user, password, and pid values for an account with spaces, not commas! For example, to create two user accounts, Paul and Suzanne, you might use the following CREATE USER statement:

CREATE USER Suzanne squeaky SF67, Paul sparky NY58; 

You use the CREATE GROUP statement to create a new group account:

CREATE GROUP group1 pid1 [, group2 
      pid2 [, ...] ] 

Group accounts don't have passwords because they're not login accounts. Group and user account names share the same "namespace," so you can't create a user and group account with the same name.

The following CREATE GROUP statement creates the Programmers group:

CREATE GROUP Programmers 89rootbeer; 

Changing passwords. You change a user account password using the ALTER USER statement:

ALTER USER user PASSWORD old_password new_password 
      

For example, you might use the following ALTER USER statement to change Suzanne's login password from plus to minus:

ALTER USER Suzanne PASSWORD minus plus

You use the ALTER DATABASE statement to change the database password:

ALTER DATABASE PASSWORD old_password new_password 

To change the database password from no password (Null) to foo, you'd use the following ALTER DATABASE statement:

ALTER DATABASE PASSWORD foo Null

Adding users to groups. You use the ADD USER statement to make a user account a member of a group. The syntax is as follows:

ADD USER user1 [, user2 [, ...] ] TO 
      group

For example, to add the Peter account to the Programmers group, you would use the following ADD USER statement:

ADD USER Peter TO Programmers

You would use the following ADD USER statement to add Paul and Suzanne to the built-in users group:

ADD USER Suzanne, Paul TO Users

Make sure you add any new user account you create using the CREATE USER statement to the built-in Users group using the ADD USER statement. If you don't add a new user account to the Users group, then you won't be able to login using the new account.

Dropping users and groups. The DROP USER statement has two forms. You use the first form to delete a user account:

DROP USER user1 [, user2 [, ...] ]

To delete the Paul user account, you would use the following DROP USER statement:

DROP USER Paul

You use the second form of the DROP USER statement to remove a user from a group:

DROP USER user1 [, user2 [, ...] ] FROM 
      group

You could use the following DROP USER statement to remove Suzanne from the Programmers group:

DROP USER Suzanne FROM Programmers

You use the DROP GROUP statement to delete a group account:

DROP GROUP group1 [, group2 [, ...] ] 

Use the following DROP GROUP statement to delete the Managers account:

DROP GROUP Managers

Granting and revoking permissions. You use the GRANT statement to assign security privileges for an object to an existing user or group account. The syntax of the GRANT statement is shown here:

GRANT privilege1 [, privilege2 [, ...] ] 
ON

{TABLE table | OBJECT object | CONTAINER 
      container}

TO account1 [, account2 [, ...] ]

The privilege can be any of the following:

·          SELECT

·          DELETE

·          INSERT

·          UPDATE

·          DROP

·          SELECTSECURITY

·          UPDATESECURITY

·          DBPASSWORD

·          UPDATEIDENTITY

·          CREATE

·          SELECTSCHEMA

·          SCHEMA

·          UPDATEOWNER

Table is the name of any table; object can be the name of any non-table object; container is the name of any object container. Valid object container names include:

·          Tables

·          Forms

·          Reports

·          Modules

·          Scripts

The Tables container includes tables, queries, views, and procedures. The Scripts container contains Access macros.

For example, the following GRANT statement grants the Programmers group the ability to view, delete, add, and update rows from tblCustomer:

GRANT SELECT, DELETE, INSERT, UPDATE

ON TABLE tblCustomer

TO Programmers

You use the REVOKE statement to revoke security privileges for an object from an existing user or group account. The syntax of the REVOKE statement is shown here:

REVOKE privilege1 [, privilege2 [, ...] ] 
      ON

{TABLE table | OBJECT object | CONTAINER 
      container}

FROM account1 [, account2 [, ...] ] 

For example, the following REVOKE statement revokes the DELETE privilege to tblCustomer from the Programmers group:

REVOKE DELETE

ON TABLE tblCustomer

FROM Programmers

Conclusion

Thanks to Jet 4.0's enhanced ANSI-SQL support you can now perform many SQL DDL operations without having to use the Access UI, or learn the somewhat confusing DDL syntax of DAO or ADOX. However, to use the new ANSI SQL-92 extensions, you must be using ADO and the Jet OLE DB provider. Perhaps the next version of Access or Jet will make the extensions available everywhere.

This article is adapted, with permission, from Access 2000 Developer's Handbook, Volume I by Ken Getz, Paul Litwin, and Mike Gilbert (SYBEX, 1999).

The file referenced in this article is available for download.

Paul Litwin is a senior consultant with MCW Technologies, focusing on application development employing Access, VB, Visual InterDev, and SQL Server. He has written/co-written several books, including Access 2000 Developer's Handbook, Volume I by Ken Getz, Paul Litwin, and Mike Gilbert, from which this article is based. Paul trains developers for Application Developers Training Company and speaks regularly at industry conferences, including MOVS. You can reach Paul at mailto:plitwin@mcwtech.com or http://www.mcwtech.com/.

Ken Getz, a senior consultant with MCW Technologies, splits his time between programming, writing, and training. Ken is co-author of several books for developers, including Access 97 Developer's Handbook [SYBEX, 1997] (with Paul Litwin and Mike Gilbert) and VBA Developer's Handbook [SYBEX, 1997] (with Mike Gilbert). He also co-wrote the training materials, travels around the United States teaching, and recorded training videos on Access 97 and VB6 for Application Developers Training Company. Ken is currently at work on Visual Basic Language Developer's Handbook, from SYBEX. In addition, Ken is a Contributing Editor for Microsoft Office & Visual Basic for Applications Developer magazine.

Copyright © 1999 Informant Communications Group. All Rights Reserved. • Site Use Agreement • Send feedback to the Webmaster • Important information about privacy