Check Out Smart Access - Its Back With A Vengance

When I first tried to create tables automatically in Access a couple of years ago, I fell back on my SQL background and went hunting for the Create Table command. But I didn't find it in the help and resorted to using more manual methods. When I was asked this question late last year, I had another look and found that all the SQL table management commands were actually supported. These commands are as follows

To find this elusive help, Open Access and under help select Contents and Index. Type CREATE in uppercase in the find box. An example of a create table command is as follows

CREATE TABLE AAA (ProductName Text, ProductDate Date);

To demonstrate how to run these in Access I have set up a form called FX_CreateTable in the demonstration database. This code shows how to build a table using the usual techniques that I employ for other SQL building software. As these table changing SQL statements are action statements, you can use the RunSql command.

DoCmd.RunSQL sqlStr

To add a new column to an existing table, I would make and run the following SQL statement

Alter TABLE AAA ADD COLUMN ProductQuantity Single;

Note that these table related commands will only work on jet databases but the syntax is pretty similar to the kind of commands that you would deploy in a SQL server/Oracle type environment.

Want to find more data types that you can use, try Access Find Help and type in "Comparison of Data Types".   This will help you find things such as autocounters etc.

Now Here is what the Access 97 help tells you because this is impossible to find in the Access 2000+ help

Create Table Statement

Creates a new table.

Note The Microsoft Jet database engine doesn't support the use of CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet database engine databases. Use the DAO Create methods instead.

Syntax

CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

The CREATE TABLE statement has these parts:

Part Description
table The name of the table to be created.
field1, field2 The name of field or fields to be created in the new table. You must create at least one field.
type The data type of field in the new table.
size The field size in characters (Text and Binary fields only).
index1, index2 A CONSTRAINT clause defining a single-field index. See the CONSTRAINT clause topic for more information on how to create this index.
multifieldindex A CONSTRAINT clause defining a multiple-field index. See the CONSTRAINT clause topic for more information on how to create this index.
Remarks

Use the CREATE TABLE statement to define a new table and its fields and field constraints. If NOT NULL is specified for a field, then new records are required to have valid data in that field.
A CONSTRAINT clause establishes various restrictions on a field, and can be used to establish the primary key. You can also use the CREATE INDEX statement to create a primary key or additional indexes on existing tables.
You can use NOT NULL on a single field, or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field, or a run-time error occurs.

Alter Table Statement

Modifies the design of a table after it has been created with the CREATE TABLE statement.

Note The Microsoft Jet database engine doesn't support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Microsoft Jet databases. Use the DAO Create methods instead.

Syntax

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] | CONSTRAINT multifieldindex} | DROP {COLUMN field I CONSTRAINT indexname} }

The ALTER TABLE statement has these parts:

Part Description
table The name of the table to be altered.
field The name of the field to be added to or deleted from table.
type The data type of field. Try ( BINARY, Boolean, Byte, Long, Currency, Date, Double, Long, LONGBINARY, LONGTEXT, SINGLE, Integer, TEXT, LONGTEXT)

size The field size in characters (Text and Binary fields only).
index The index for field. See the CONSTRAINT clause topic for more information on how to construct this index.
multifieldindex The definition of a multiple-field index to be added to table. See the CONSTRAINT clause topic for more information on how to construct this clause.
indexname The name of the multiple-field index to be removed.

Remarks

Using the ALTER TABLE statement, you can alter an existing table in several ways. You can:

· Use ADD COLUMN to add a new field to the table. You specify the field name, data type, and (for Text and Binary fields) an optional size. For example, the following statement adds a 25-character Text field called Notes to the Employees table:

ALTER TABLE Employees ADD COLUMN Notes TEXT(25)

You can also define an index on that field. For more information on single-field indexes, see the CONSTRAINT clause topic.

If you specify NOT NULL for a field, then new records are required to have valid data in that field.

· Use ADD CONSTRAINT to add a multiple-field index. For more information on multiple-field indexes, see the CONSTRAINT clause topic.
· Use DROP COLUMN to delete a field. You specify only the name of the field.
· Use DROP CONSTRAINT to delete a multiple-field index. You specify only the index name following the CONSTRAINT reserved word.

Notes

· You can't add or delete more than one field or index at a time.
· You can use the CREATE INDEX statement to add a single- or multiple-field index to a table, and you can use ALTER TABLE or the DROP statement to delete an index created with ALTER TABLE or CREATE INDEX.
· You can use NOT NULL on a single field, or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field, or a run-time error occurs.

Create Index statement

Creates a new index on an existing table.

Note For non-Microsoft Jet databases, the Microsoft Jet database engine doesn't support the use of CREATE INDEX (except to create a pseudo index on an ODBC linked table) or any of the data definition language (DDL) statements. Use the DAO Create methods instead. For more information, see the Remarks section.

Syntax

CREATE [ UNIQUE ] INDEX index ON table (field [ASC|DESC][, field [ASC|DESC], ...]) [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

The CREATE INDEX statement has these parts:

Part Description
index The name of the index to be created.
table The name of the existing table that will contain the index.
field The name of the field or fields to be indexed. To create a single-field index, list the field name in parentheses following the table name. To create a multiple-field index, list the name of each field to be included in the index. To create descending indexes, use the DESC reserved word; otherwise, indexes are assumed to be ascending.
Remarks

To prohibit duplicate values in the indexed field or fields of different records, use the UNIQUE reserved word.
In the optional WITH clause, you can enforce data validation rules. You can:

· Prohibit Null entries in the indexed field or fields of new records by using the DISALLOW NULL option.
· Prevent records with Null values in the indexed field or fields from being included in the index by using the IGNORE NULL option.
· Designate the indexed field or fields as the primary key by using the PRIMARY reserved word. This implies that the key is unique, so you can omit the UNIQUE reserved word.

You can use CREATE INDEX to create a pseudo index on a linked table in an ODBC data source, such as SQL Server, that does not already have an index. You don't need permission or access to the remote server to create a pseudo index, and the remote database is unaware of and unaffected by the pseudo index. You use the same syntax for both linked and native tables. This can be especially useful to create an index on a table that would ordinarily be read-only due to lack of an index.

You can also use the ALTER TABLE statement to add a single- or multiple-field index to a table, and you can use the ALTER TABLE statement or the DROP statement to remove an index created with ALTER TABLE or CREATE INDEX.

Note Don't use the PRIMARY reserved word when you create a new index on a table that already has a primary key; if you do, an error occurs.

Constraint Clause

A constraint is similar to an index, although it can also be used to establish a relationship with another table.

You use the CONSTRAINT clause in ALTER TABLE and CREATE TABLE statements to create or delete constraints. There are two types of CONSTRAINT clauses: one for creating a constraint on a single field and one for creating a constraint on more than one field.

Note The Microsoft Jet database engine doesn't support the use of CONSTRAINT, or any of the data definition language (DDL) statements, with non-Microsoft Jet databases. Use the DAO Create methods instead.

Syntax

Single-field constraint:

CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL | REFERENCES foreigntable [(foreignfield1, foreignfield2)]}

Multiple-field constraint:

CONSTRAINT name {PRIMARY KEY (primary1[, primary2 [, ...]]) | UNIQUE (unique1[, unique2 [, ...]]) | NOT NULL (notnull1[, notnull2 [, ...]]) | FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]}

The CONSTRAINT clause has these parts:

Part Description
name The name of the constraint to be created.
primary1, primary2 The name of the field or fields to be designated the primary key.
unique1, unique2 The name of the field or fields to be designated as a unique key.
notnull1, notnull2 The name of the field or fields that are restricted to non-Null values.
ref1, ref2 The name of a foreign key field or fields that refer to fields in another table.
foreigntable The name of the foreign table containing the field or fields specified by foreignfield.
foreignfield1, foreignfield2 The name of the field or fields in foreigntable specified by ref1, ref2. You can omit this clause if the referenced field is the primary key of foreigntable.
Remarks

You use the syntax for a single-field constraint in the field-definition clause of an ALTER TABLE or CREATE TABLE statement immediately following the specification of the field's data type.
You use the syntax for a multiple-field constraint whenever you use the reserved word CONSTRAINT outside a field-definition clause in an ALTER TABLE or CREATE TABLE statement.
Using CONSTRAINT, you can designate a field as one of the following types of constraints:

· You can use the UNIQUE reserved word to designate a field as a unique key. This means that no two records in the table can have the same value in this field. You can constrain any field or list of fields as unique. If a multiple-field constraint is designated as a unique key, the combined values of all fields in the index must be unique, even if two or more records have the same value in just one of the fields.
· You can use the PRIMARY KEY reserved words to designate one field or set of fields in a table as a primary key. All values in the primary key must be unique and not Null, and there can be only one primary key for a table.

Note Don't set a PRIMARY KEY constraint on a table that already has a primary key; if you do, an error occurs.

· You can use the FOREIGN KEY reserved words to designate a field as a foreign key. If the foreign table's primary key consists of more than one field, you must use a multiple-field constraint definition, listing all of the referencing fields, the name of the foreign table, and the names of the referenced fields in the foreign table in the same order that the referencing fields are listed. If the referenced field or fields are the foreign table's primary key, you don't have to specify the referenced fields — by default, the database engine behaves as if the foreign table's primary key is the referenced fields.

DROP Statement

Deletes an existing table from a database or deletes an existing index from a table.

Note The Microsoft Jet database engine doesn't support the use of DROP, or any of the DDL statements, with non-Microsoft Jet databases. Use the DAO Delete method instead.

Syntax

DROP {TABLE table | INDEX index ON table}

The DROP statement has these parts:

Part Description
table The name of the table to be deleted or the table from which an index is to be deleted.
index The name of the index to be deleted from table.
Remarks

You must close the table before you can delete it or remove an index from it.
You can also use ALTER TABLE to delete an index from a table.
You can use CREATE TABLE to create a table and CREATE INDEX or ALTER TABLE to create an index. To modify a table, use ALTER TABLE.

Other Pages at VB123.com

Read the Alter Tables article as it appeared in Smart Access
Samples of how to backup and recover your Access database
Lookup Tables - Getting Rid Of Junk data
Replace Your File API’s With The FileDialog Object

Related Documents Outside VB123.com
How to recover a deleted table
Dump table details using VBA
More on Alter Table statement at Microsoft << See unicode compression discussions here
Alter Table Only works with ADO, click here for DAO alternative

Thanks for reading this and do not forget to click on the button for your next tip

Garry Robinson    

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals