Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Jet 4.0 ANSI-92 Extensions

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.

Table Extensions

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

Creating Defaults

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.

Creating Check Constraints

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.

Implementing Cascading Referential Integrity

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.

Controlling Fast Foreign Keys

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}]

Implementing Unicode String Compression

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 [(

length )] WITH COMPRESSION

Controlling Autonumber Fields

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.

View and Stored Procedures Extensions

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

Transaction Extensions

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 .

Security Extensions

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."