"Learning about the Access Data Definition query allows you to have full control on how to dynamically manage database objects including creating tables and fields..."
Data Definition query is used to create and manage tables, fields, indexes, their data types and relationship joins which can be executed like any normal query or attached to VBA (Visual Basic for Application) code.
This technique is not often used especially for the general user who will typically build tables, fields and data types using the Access tools provided (and as already explained in this website).
This is for the more advanced user who has a good grasp of SQL focusing on DDL (Data Definition Language) compared to more popular DML (Data Manipulation Language); more commonly referred as standard SQL (queries already mentioned in this website).
Note: You will need to know the SQL language (syntax) to create this type of query.
Data Definition query example
In the following example, I'm going to create a table called Contacts and creating 5 new fields with a specific data type and field size (where applicable) and then assign a primary key index to the first field.
I'm using Access 2007...
Step 1: Create a new query by locating the Create tab from the Ribbon bar clicking on the Query Design icon from the Other section.
As in a normal new query, you see the Show Table pop-up window which can be closed (as it's not required).
From the Ribbon bar, you will see the Design tab and various icons in the Query Type section where you click on the Data Definition icon.
The main view switches from the design grid (QBE grid) to the SQL only view.
Step 2: At this point, you need to write some SQL and the keyword for my example will include CREATE TABLE and CONSTRAINT [Index] PRIMARY KEY.
In addition, I need to set every new field to a specific data type and set a field size (where applicable). The data types required include Integer, Date and VarChar
The following SQL is written into the SQL view:
CREATE TABLE Contacts (ContactID Integer, FirstName varchar(30), LastName varchar(30), DateOfBirth Date, Email varchar(100), CONSTRAINT [Index1] PRIMARY KEY (ContactID));
Run the query and the table is created which can be viewed in the Navigation Pane.
To know more about the SQL language, take a look on the topic about SQL syntax language and browse to the keywords in question.
There are no warnings when creating this kind of table and if you attempt to re-run the same query a message prompt will appear informing you informing you this table (name) already exists.
The simple example above starts to show what SQL can do for you though one could argue why not just create the same table using the Access table design tool.
Some developers use SQL with VBA (Visual Basic for Applications) to code dynamically and on the fly such objects that adds better control to database management and security.
Other keywords that can be used in this type of DDL (Data Definition Query Language) include:
Of course, there are many more keywords not listed above and you will need to get hold of further references for more information.
The Data Definition query is not the only way to create a table. If you use a Make Table query instead it will create the table, fields and default data types from other data sources which Access will normally prompt you before proceeding!
Return from Data Definition query to Access Queries
Return from Data Definition query to About Access Databases home page