Recipe 1.15 Use a Query to Create a New Table Complete with Indexes
1.15.1 Problem
You know how to create a table from a
make-table query, but when you create a table in this way it has no
primary key or any other indexes. Furthermore, you can only create a
new table with a structure based on that of an existing table.
You'd like a way to create a table on the fly with
the data types and field sizes you want and with appropriate indexes.
1.15.2 Solution
Access provides the data
definition language (DDL) query, which is used to programmatically
create or modify tables. It is one of the SQL-specific queries, which
can be created only using SQL view. This solution shows you how to
create and modify table definitions using DDL queries.Follow these steps to create a table using a DDL query:
- Design your table, preferably on paper, deciding which fields and
indexes you wish to create. For example, before creating
qryCreateClients, we came up with the design for tblClients shown in
Table 1-8.
FieldName | DataType | FieldSize | Index |
---|---|---|---|
ClientID | AutoNumber | Long Integer/Increment | Yes, primary key |
FirstName | Text | 30 | Yes, part of ClientName index |
LastName | Text | 30 | Yes, part of ClientName index |
CompanyName | Text | 60 | Yes |
Address | Text | 80 | No |
City | Text | 40 | No |
State | Text | 2 | No |
ZipCode | Text | 5 | No |
- Create a new query. Click on Close at the Add Table dialog. Select
Query SQL Specific Data Definition. This will
place you in SQL view. - Enter a
CREATE TABLE SQL statement. To
create the sample table tblClients, enter the following SQL:CREATE TABLE tblClients
(ClientID AutoIncrement CONSTRAINT PrimaryKey PRIMARY KEY,
FirstName TEXT (30),
LastName TEXT (30),
CompanyName TEXT (60) CONSTRAINT CompanyName UNIQUE,
Address TEXT (80),
City TEXT (40),
State TEXT (2),
ZipCode TEXT (5),
CONSTRAINT ClientName UNIQUE (LastName, FirstName) ); - Save your query and run it by selecting Query Run or
clicking on the exclamation point icon on the toolbar. You should now
see the newly created table in the database container.
To see how this works, open 01-15.MDB . Note that
there are no sample tables in this database. Open the sample DDL
query, qryCreateClients (see Figure 1-43). Select
Query Run or click on the exclamation point icon on the
toolbar to execute the DDL query. The tblClients table will be
created, complete with a primary key and two other indexes.
Figure 1-43. A sample DDL query and the table it creates
1.15.3 Discussion
When you run a DDL query, Access reads through the
query's clauses and creates a table according to
your specifications. This allows you to precisely control the
structure of the table and its indexes.A DDL query can contain only one data-definition statement. The five
types of data-definition statements are:
Creates a table
Adds a new field or constraint to an
existing table (a constraint creates an index on a field or group of
fields)
Deletes a table from a database
Creates an index for a field or group
of fields
Removes an index from a field or group
of fields
Note that we specified the
lengths of most of the text fields in the sample query to save space.
If you don't specify a length for a text field in a
DDL query, Access will assign it the maximum length of 255
characters, but that length won't necessarily affect
the size of the database. The field length is just a
maximumthe space is not used unless it is needed.If you wish to create field names with
embedded spaces, you'll need to surround the names
with brackets; otherwise, the brackets are optional.Like make-table queries, DDL queries do
not automatically overwrite an existing table. However, unlike
make-table queries, you aren't offered the option of
overwriting the existing table if you want to. If you need to
overwrite an existing table when running a DDL query, first execute
another DDL query containing a DROP
TABLE statement.After
you create (or delete) a table with a DDL query, the new table
won't immediately appear in (or disappear from) the
database window. To refresh the display and see the change you made,
click on another object type in the database window (for example,
Forms) and then on the Table tab again.
|
with indexes using Data Access Objects (DAO) or ADOX, using VBA code,
and you can use DAO QueryDefs or ADO commands to execute your DDL
statements in code.
|