ACC: Create and Drop Tables and Relationships Using SQL DDL

ID: Q116145

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

Microsoft Access includes a Data Definition Language (DDL) that you can use to create and delete (drop) tables and relationships. These same operations can also be performed with Visual Basic for Applications (or Access Basic in version 2.0) by using data access objects (DAO). This article describes some of the operations available in DDL.

NOTE: All the examples in this article assume that the statements are added to a Microsoft Access query and are run by clicking Run on the Query menu.

MORE INFORMATION

To use data definition statements, create a new query. Then click SQL Specific, and then Data Definition on the Query menu. Enter your data definition statement in the Data Definition Query window, then run the query by clicking Run on the Query menu.

To create a table with a PrimaryKey field with an AutoNumber data type (or Counter data type in version 2.0), and a field called MyText with a Text data type and a length of 10, enter the following statement in the Data Definition Query window and then run the query.

NOTE: In the following sample queries, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these queries.


   CREATE TABLE Table1 (Id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, _
   MyText TEXT (10))

The Foreign Key side of a relationship does not require a PrimaryKey and can be created by running the following query. This query creates a table with one field with a Long data type and another field with a Text data type with a default size of 255:


   CREATE TABLE Table2 (Id LONG, MyText TEXT)

After creating both tables, running the following query creates a one- to-many relationship between Table1 and Table2, with Table1 as the primary side of the relationship:


   ALTER TABLE Table2 ADD CONSTRAINT Relation1 FOREIGN KEY ([Id]) _
   REFERENCES Table1 ([Id])

To delete the relationship between the tables, run the following query:


   ALTER TABLE Table2 DROP CONSTRAINT Relation1

To delete Table1, run the following query:


   DROP TABLE Table1

To delete Table2, run the following query:


   DROP TABLE Table2

You can also run DDL statements using the RunSQL action. To run a DDL statement in code, use a procedure similar to the following example:


   Sub ExecuteSQLDDL (SQLString As String)
      Dim db As Database, qd As QueryDef
      Set db = DBEngine.Workspaces(0).Databases(0)
      Set qd = db.CreateQueryDef("")
      qd.SQL = SQLString
      qd.Execute
      db.Close
   End Sub

To call this Sub procedure, use the following syntax:


   ExecuteSQLDDL "DROP TABLE Table1"

REFERENCES

For more information about DDL queries, search the Help Index for "data- definition queries," or ask the Microsoft Access 97 Office Assistant.

Keywords : kbusage QrySqldd
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbinfo