Data Definition Language Component
The SQL language is broken up into three components: Data Definition Language, Data Manipulation Language, and Data Control Language. Recall that the Data Definition Language (DDL) component is used to create tables and establish relationships among tables, and the Data Manipulation Language (DML) component is used to manage the database by performing such operations as retrieving, updating, deleting, and navigating through data. The Data Control Language (DCL) component is used to provide security to data in a database. The commands within each of these components are as follows:
DDL DML DCL CREATE TABLE INSERT INTO ALTER DATABASE DROP TABLE SELECT INTO CREATE GROUP ALTER TABLE UPDATE DROP GROUP CREATE INDEX DELETE CREATE USER SELECT ALTER USER UNION DROP USER TRANSFORM ADD USER PARAMETER GRANT PRIVILEGE REVOKE PRIVILEGE
Note: The DCL commands can only be executed in the Visual Basic environment of Microsoft Access. An error message will be returned if used through the Access SQL view user interface. Visual Basic is the host language for the Jet DBMS, which handles the translation of database queries into Access SQL.
In this tutorial, we will focus on the implementation of the CREATE TABLE, ALTER TABLE, INSERT INTO, SELECT INTO, UPDATE, and DELETE statements. Let's begin by learning how to create a table.
Enable a Blocked Query in Microsoft Access
Before we begin to create and populate tables with records, let's ensure that your database is not in Disabled mode. Disabled mode is a security feature designed to block specific types of SQL queries.
Microsoft Access 2007 defaults to Disabled mode when you open a database that is not saved in a trusted location or if you chose not to trust the database. When Microsoft Access is in Disabled mode you will not be able to run action, append, update, delete, or make-table queries.
To disable Disabled mode, look for messages similar to the following in the Access status bar:
- This action or event has been blocked by Disabled mode.
- Certain content in this database has been disabled.
To enable blocked content, click Options on the Access status bar. The Microsoft Office Security Options dialog box will appear. Click Enable this content, and then click OK.
Note: If you don't see a security message on the Access status bar, click the Database Tools tab, and in the Show/Hide group, click Message Bar.
In this tutorial:
- Creating Tables and Inserting Records
- Data Definition Language Component
- CREATE TABLE Syntax
- SQL Data Types
- NULL/NOT NULL Constraint
- PRIMARY KEY Constraint
- FOREIGN KEY Constraint
- Adding Constraints to Existing Tables
- Constraint Syntax
- Inserting Records
- Inserting Data without Specifying Column Names