Access Cookbook

Use a Query to Create a New Table Complete with Indexes

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.

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:

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

   Table 1-8. Design for tblClients

   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

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

3. Enter a CREATE TABLE SQL statement. ...