| You can create new tables in your current database with DDL reserved words, which are identical for Jet 4.0 and SQL Server 7.0+. Using SQL to create new tables is of primary interest to developers, not users, of Access applications because it's much easier to create new tables with the Jet or SQL Server graphical table design tools than writing the equivalent DDL statements. In some cases, such as emulating Jet crosstab queries with T-SQL stored procedures, however, you must create tables with SQL statements. Note Jet supports ANSI-92 DDL statements only in VBA code and requires use of the OLE DB provider for Jet 4.0 to establish an ADODB.Connection object to the Jet database. You must add references in the VBA editor to the Microsoft ActiveX Data Objects 2.7 Library (Msado15.dll) and Microsoft ADO Ext. 2.7 for DDL and Security (Msadox.dll) to support Jet 4.0 DDL. Chapter 28 explains how to add these references in the VBA editor.
Following is a brief description of the basic ANSI SQL-92 DDL syntax for creating, altering, and dropping (deleting) tables and related objects: CREATE TABLE table_name (field_name data_type [(field_size)][, field_name data_type...]) creates a new table with the fields specified by a comma-separated list. Properties of fields are space delimited, so you need to enclose entries for field names with spaces in square brackets ([]). For Jet SQL, the data_type can be any valid Jet SQL field data type, such as TEXT(field_size) or INTEGER. T-SQL accepts any valid SQL Server data, such as int, datetime, nvarchar(field_size), and text. (The default field data_type for Jet is Text and for SQL Server is char. The default field_size argument value is 50 characters for Jet and SQL Server.) Tip If you don't specify a data_type for a field, the default values for data_type and field_size apply. You can change the default field data type and size in the Tables/Queries page of the Options dialog for Jet and ADP.
Note If you use the Jet TEXT modifier without a field_size argument (in parentheses), Jet interprets TEXT as the Memo field data type for conformance to SQL Server's text data type. Jet SQL includes many new data type modifiers, such as CHARACTER, VARCHAR, NATIONAL CHAR, NATIONAL CHAR VARYING, and others for localization and Unicode preferences. The corresponding SQL Server data types are char, varchar, nchar, and nvarchar.
CONSTRAINT constraint_name {PRIMARY KEY|UNIQUE|REFERENCES foreign_table [(foreign_field)]} establishes DRI for the table. Jet and SQL Server create an index on the field name that precedes the expression. You can specify the index as the PRIMARY KEY or as a UNIQUE index. SQL Server lets you specify [CLUSTERED|NONCLUSTERED] for a PRIMARY KEY or UNIQUE index. You also can establish a relationship between the field and the field of a foreign table with the REFERENCES foreign_table [foreign_field] entry. (The [foreign_field] item is required if the foreign_field is not a primary-key field.) CHECK (expression) creates an additional constraint that's similar to but more flexible than Jet's table-level validation. The expression argument can compare values obtained from other tables by means of a SELECT statement. CREATE [UNIQUE] INDEX index_name ON table_name (field_name [ASC|DESC][, field_name [ASC|DESC], ...]) [WITH {PRIMARY|DISALLOW NULL|IGNORE NULL}] creates an index on one or more fields of a table. If you specify the WITH PRIMARY modifier, UNIQUE is assumed (and not required). You can only create one primary-key index/constraint on a table. DISALLOW NULL prevents the addition of records with NULL values in the indexed field; IGNORE NULL doesn't index records with NULL field_name values. ALTER TABLE lets you add new fields (ADD COLUMN field_name...) or delete existing fields (DROP COLUMN field_name...). ALTER COLUMN table_name (field_name data_type [field_size]) lets you change the properties of a single column. DROP COLUMN column_name ON table_name deletes the column from a table specified by table_name. DROP INDEX index_name ON table_name deletes the index from a table specified by table_name. DROP TABLE table_name deletes a table from the database.
The syntax examples of the preceding list, other than DROP COLUMN, DROP INDEX, and DROP TABLE, cover only the basic syntax common to Jet and SQL Server objects. The "Data Definition Language" topic under the "Microsoft Jet SQL Reference" node of online help's Contents pane provide more complete syntax examples. For a full description of T-SQL's CREATE TABLE statement, search for CREATE TABLE in the Index pane of SQL Server Books Online. |
