Jet 4.0, the version of Jet that ships with Access 2000 and later, includes expanded support for the ANSI-92 standard. Although these extensions are not available via the Access user interface, you can tap into them using ADO code. This section covers the extensions and the functionality they afford you. Because I have not yet covered ADO, you might want to refer to Chapter 14 to better understand the examples. For now, you need to understand that the code examples in this section use the ADO Command object to execute SQL statements that create and manipulate database objects.
Six table extensions are included with Jet 4.0. These extensions provide you with the ability to
Create defaults
Create check constraints
Set up cascading referential integrity
Control fast foreign keys
Implement Unicode string compression
Better control autonumber fields
The DEFAULT keyword can be used with the CREATE TABLE statement. The syntax is
DEFAULT (
value )
Here's an example:
Sub CreateDefault() Dim cmd As ADODB.Command Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "CREATE TABLE tblCustomers " & _ "(CustomerID LONG CONSTRAINT CustomerID PRIMARY KEY, " & _ "CompanyName TEXT (50), IntroDate DATETIME, " & _ "CreditLimit CURRENCY DEFAULT 5000)" cmd.Execute End Sub
Notice first that ADO is used to execute the SQL statement. This is because the DEFAULT keyword is not accessible via the use interface. The CreditLimit field includes a DEFAULT clause that sets the default value of the field to 5000.
The CHECK keyword can be used with the CREATE TABLE statement. It allows you to add business rules for a table. Unlike field- and table-level validation rules that are available via the user interface, check constraints can span tables. The syntax for a check constraint is
[CONSTRAINT [
name ]] CHECK (
search_condition )
Here's an example:
Sub CreateCheckConstraint() Dim cmd As ADODB.Command Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "CREATE TABLE tblCustomers " & _ "(CustomerID LONG CONSTRAINT CustomerID PRIMARY KEY, " & _ "CompanyName TEXT (50), IntroDate DATETIME, " & _ "CONSTRAINT IntroDateCheck CHECK (IntroDate <= Date()), " & _ "CreditLimit CURRENCY DEFAULT 5000)" cmd.Execute End Sub
The example creates a check constraint on the IntroDate field that limits the value entered in the field to a date on or before today's date.
The ANSI-92 extensions can also be used to establish cascading referential integrity. The syntax is
CONSTRAINT
name FOREIGN KEY (
column1 [,
column2 [,...]]) REFERENCES
foreign-table [(
foreign-column1 [,
foreign-column2 [,...]])] [ON UPDATE {NO ACTION|CASCADE}] [ON DELETE {NO ACTION|CASCADE}]
Without the CASCADE options, the primary key field cannot be updated if the row has child records, and the row on the one side of the one-to-many relationship cannot be deleted if it has children.
Whenever you join two tables in a one-to-many relationship, Access automatically creates an index on the foreign key field (the many side of the relationship). This is generally a good thing. It is only bad if the foreign key contains a lot of Nulls. In that case, the index serves only to degrade performance rather than improve it. Fortunately, using the Jet 4.0 ANSI-92 extensions, and the NO INDEX keywords, you can create the foreign key without the index. Here's the syntax:
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}]
Just as you can implement Unicode string compression using the user interface, you can also implement it in code. The syntax is
Column string-data-type [(
Using the Jet 4.0 ANSI-92 extensions, you can change both the autonumber seed and increment. The syntax is
Column AUTOINCREMENT (
seed, increment )
Here's an example:
Sub CreateAutonumber() Dim cmd As ADODB.Command Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "CREATE TABLE tblCustomers " & _ "(CustomerID AUTOINCREMENT (100000,1), " & _ "CompanyName TEXT (50), IntroDate DATETIME, " & _ "CreditLimit CURRENCY DEFAULT 5000)" cmd.Execute End Sub
The code creates an auto-increment field called CustomerID. The starting value is 100000. The field increments by 1. In addition to the added support for seed value and increment value, the Jet 4.0 ANSI-92 extensions allow you to retrieve the last-assigned autonumber value. Here's how it works:
Sub LastAutonumber() Dim cmd As ADODB.Command Dim rst As ADODB.Recordset Set cmd = New ADODB.Command Set rst = New ADODB.Recordset cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "INSERT INTO tblCustomers " & _ "(CompanyName, IntroDate, CreditLimit) " & _ "VALUES ('Test Company', #1/1/2001#, 100) " cmd.Execute rst.ActiveConnection = CurrentProject.Connection rst.Open ("SELECT @@Identity as LastCustomer FROM tblCustomers") MsgBox rst("LastCustomer") End Sub
The code first inserts a row into the tblCustomers table. It then opens a recordset and retrieves the @@Identity value. As with SQL Server, this @@Identity variable contains the value of the last assigned autonumber.
The Jet 4.0 ANSI-92 extensions allow you to create views and stored procedures similar to those found in SQL Server. Essentially, these views and stored procedures are Access queries that are repackaged to behave like their SQL Server counterparts. Although stored as queries, the views and stored procedures that you create are not visible via the user interface. You can execute them just like saved queries. The syntax to create a view looks like this:
CREATE VIEW
view-name [(
field1 [(,
field2 [,...]])] AS
select-statement
Here's an example:
Sub CreateView() Dim cmd As ADODB.Command Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "CREATE VIEW vwClients " & _ "AS SELECT ClientID, CompanyName " & _ "FROM tblClients" cmd.Execute End Sub
As covered in Chapter 14, use the following code to execute the view:
Sub ExecuteView() Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "vwClients" MsgBox rst.RecordCount End Sub
The syntax to create a stored procedure is
CREATE PROC[EDURE] procedure [(param1 datatype1 [,param2 datatype2 [,...]])] AS sql-statement
Here's an example:
Sub CreateStoredProc() Dim cmd As ADODB.Command Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "CREATE PROCEDURE procClientGet " & _ "(ClientID long) " & _ "AS SELECT ClientID, CompanyName " & _ "FROM tblClients " & _ "WHERE ClientID = ClientID" cmd.Execute End Sub
Use the EXECUTE statement, as shown in the following code, to execute the stored procedure:
Sub ExecuteStoredProc() Dim rst As ADODB.Recordset Dim cmd As Command Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "EXECUTE procClientGet 1" Set rst = cmd.Execute MsgBox rst("CompanyName") End Sub
Using Jet 4.0 ANSI-92 security extensions, you can create transactions that span an ADO connection. These extensions are intended to augment, rather than replace, ADO transactions. You use BEGIN TRANSACTION to start a transaction, COMMIT TRANSACTION to commit a transaction, and ROLLBACK [TRANSACTION] to cancel a transaction. Transactions are covered in detail in
Alison Balter's Mastering Access 2002 Enterprise Development .
Jet 4.0 provides numerous ANSI-92 security extensions. These extensions allow you to add and remove users and groups, as well as administer passwords and permissions. The ANSI-92 security extensions are covered in detail in Chapter 28, "Advanced Security Techniques."