HOWTO: Common DDL SQL for the Microsoft Access Database Engine

• The DAO SDK included with: - Microsoft Visual C++, 32-bit Editions, version 5.0
• The Microsoft Foundation Classes (MFC) included with: - Microsoft Visual C++, 32-bit Editions, version 5.0
• Microsoft Access versions 7.0, 97

SUMMARY

This article demonstrates some common DDL (Data Definition Language) SQL statements for the Microsoft Jet database engine.

MORE INFORMATION

Microsoft Jet provides a full set of Data Definition Language SQL statements, which can be used to create, modify, and delete tables, indexes, and relationships in a Microsoft Access database file. You can use this DDL with the MFC database classes to manipulate an Access database. This article lists some common examples of this category of SQL statements.

You can execute a single DDL statement in this article using the following syntax with the MFC DAO classes:

   // Open database file.
   CDaoDatabase db;
   db.Open( _T("C:\\MyDatabase.mdb") );

   // Set strSQL to desired DDL statement.
   CString strSQL;
   strSQL = _T("CREATE TABLE Simple (ID long)" );

   // Execute DDL statement.
   try
   {
      db.Execute( strSQL, dbFailOnError );
   }
   catch ( CDaoException *e )
   {
      // Display errors (simple example).
      AfxMessageBox( e->m_pErrorInfo->m_strDescription,
                     MB_ICONEXCLAMATION );
      e->Delete();
   }

   // Open database file.
   CDatabase db;
   db.OpenEx( _T("DSN=MyAccessDB"), CDatabase::noOdbcDialog );

   // Set strSQL to desired DDL statement.
   CString strSQL;
   strSQL = _T("CREATE TABLE Simple (ID long)" );

   // Execute DDL statement.
   try
   {
      db.ExecuteSQL( strSQL );
   }
   catch ( CDBException *e )
   {
      // Display errors (simple example).
      AfxMessageBox( e->m_strError,
                     MB_ICONEXCLAMATION );
      e->Delete();
   }

The CREATE TABLE DDL Statement

The following create table statement creates a test table with all of the DDL available to Microsoft Access data types:

   CREATE TABLE TestAllTypes
   (
      MyText       TEXT(50),
      MyMemo       MEMO,
      MyByte       BYTE,
      MyInteger    INTEGER,
      MyLong       LONG,
      MyAutoNumber COUNTER,
      MySingle     SINGLE,
      MyDouble     DOUBLE,
      MyCurrency   CURRENCY,
      MyReplicaID  GUID,
      MyDateTime   DATETIME,
      MyYesNo      YESNO,
      MyOleObject  LONGBINARY,
      MyBinary     BINARY(50)
    )

You can create a table with a single-field primary key with a single DDL statement. The following statement creates a table named TestPrimaryKey with a primary key on the MyID field named PK_MyID:

   CREATE TABLE TestPrimaryKey
   (
      MyID LONG CONSTRAINT PK_MyID PRIMARY KEY,
      FirstName TEXT(20),
      LastName  TEXT(20)
   )

The ALTER TABLE DDL Statement

Microsoft Access DDL supports the ALTER TABLE DDL statement. This statement is useful when you need to remove or add a field to an existing table.

Note that this statement won't let you alter an existing field in an Access table (for example, to change the field's data type).

The following DDL removes the column named MoreInfo from the table TooManyFields:

   ALTER TABLE TooManyFields DROP COLUMN MoreInfo

   ALTER TABLE NotEnoughFields ADD COLUMN ExtraInfo Text(255)

The following SQL statement creates two tables (Cars and Colors) and then creates a relationship between the Cars table and the Colors table on the ColorID field (each car can have only one color). Note that there are three separate DDL statements, which must be executed one at a time:

   CREATE TABLE Cars
   (
      CarID LONG,
      CarName TEXT(50),
      ColorID LONG
   )

   CREATE TABLE Colors
   (
      ColorID LONG CONSTRAINT PK_Colors PRIMARY KEY,
      ColorName TEXT(50)
   )

   ALTER TABLE Cars
   ADD CONSTRAINT MyColorIDRelationship
   FOREIGN KEY (ColorID) REFERENCES Colors (ColorID)

The CREATE INDEX DDL Statement

The CREATE INDEX DDL statement is used to create additional indexes on an existing table.

The following DDL statement adds a single-field, non-unique, ascending index named MyStateIndex to the field State in the table Addresses:

   CREATE INDEX MyStateIndex
   ON Addresses
   (
      State ASC
   )

   CREATE UNIQUE INDEX MyFullNameIndex
   ON Addresses
   (
      FirstName ASC,
      LastName  ASC
   )

The following DDL statement creates a single-field, unique, descending index named MySalaryIndex on the field Salary in the table HRInfo:

   CREATE UNIQUE INDEX MySalaryIndex
   ON HRInfo
   (
      Salary DESC
   )
   WITH DISALLOW NULL

The DROP DDL Statement

The DROP DDL statement is used to delete existing tables or indexes.

The following DDL statement permanently deletes the table named TempTable:

   DROP TABLE TempTable

   DROP INDEX MyUnusedIndex ON OverIndexedTable

REFERENCES

For more information about the Microsoft Jet DDL syntax, refer to the "Microsoft Jet Database Engine Programmer's Guide, Second Edition," Chapter 3, "Data Definition and Integrity."

Keywords          : MfcDatabase kbcode
Component         : dao jet
Technology        : odbc
Version           : WINDOWS: 7.0, 97; Winnt: 5.0
Platform          : WINDOWS winnt
Issue type        : kbhowto