To use data definition statements, create a new query. Then on the
Query menu, point to
SQL Specific, and then click
Data Definition. Enter your data definition statement in the
Data Definition Query window, and then run the query by clicking
Run on the
Query menu.
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, enter 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 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 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"