The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills. This article applies only to a Microsoft Access database (.mdb). SUMMARY
Microsoft Access includes a Data Definition Language (DDL) that you can use to create and delete (drop) tables and relationships. You can also do these same operations by using Data Access Objects (DAO) in Visual Basic for Applications. This article describes some of the operations available in DDL.
MORE INFORMATION
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.
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 Relation1To delete Table1, run the following query: DROP TABLE Table1To delete Table2, run the following query: DROP TABLE Table2You can also run DDL statements using the RunSQL action. To run a DDL statement in code, use a procedure similar to the following example: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.
To call this Sub procedure, use the following syntax:
ExecuteSQLDDL "DROP TABLE Table1" REFERENCESFor more information about DDL queries, click Microsoft Access Help on the Help menu, type "data-definition queries" in the Office Assistant or the Answer Wizard, and then click Search to view the topic. Additional query words:
Keywords : kbusage kbdta QrySqldd |
Last Reviewed: May 13, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |