How to create and drop tables and relationships by using SQL Data Definition Language (DDL) in Microsoft Access

Article ID: 291539 - View products that this article applies to.
This article was previously published under Q291539
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.
For a Microsoft Access 2000 version of this article, see 209037
(http://support.microsoft.com/kb/209037/EN-US/ )
.
For a Microsoft Access 97 version of this article, see 116145
(http://support.microsoft.com/kb/116145/ )
.
Expand all | Collapse all

SUMMARY

Microsoft Access includes a Data Definition Language (DDL) that you can use to create and delete (drop) tables and relationships. You can also perform these same operations by using Data Access Objects (DAO) in Visual Basic for Applications. This article describes some of the operations available in DDL.

NOTE: All the examples in this article assume that you add the statements to an Access query and that you run them by clicking Run.

MORE INFORMATION

To use data definition statements, follow these steps:
  1. Create a new query in a Access database.
  2. On the Query menu, point to SQL Specific, and then click Data Definition.

    Note In Access 2007, click Data Definition in the Query Type group on the Design tab.
  3. Enter your data definition statement in the Data Definition Query window, and then run the query by clicking Run on the Query menu.

    Note In Access 2007, enter your data definition statement in the Data Definition Query window, and then run the query by clicking Run in the Results group on the Design tab.
To create a table with a PrimaryKey field with an AutoNumber data type and a field called MyText with a Text data type and a length of 10, type the following statement in the Data Definition Query window, and then run the query.
CREATE TABLE Table1 (Id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, MyText TEXT (10))
The foreign key side of a relationship does not require a primary key 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 you create 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:

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.


Sub ExecuteSQLDDL (SQLString As String)
   Dim db As DAO.Database, qd As DAO.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"

Properties

Article ID: 291539 - Last Review: March 29, 2007 - Revision: 5.1
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbinfo kbexpertiseinter kbvba kbprogramming kbhowto KB291539
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top