Try:
HOWTO: Common DDL SQL for the Microsoft Access Database Engine
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;180841
This example puts many of the possibilities together, demonstrating:
- creating tables,
- specifying various field types including an AutoNumber,
- a primary key index,
- text field with compression,
- setting the Required property,
- setting a Default Value,
- setting a unique index on a field,
- setting a multi-field index on the table,
- creating a relation between tables (with foreign key index),
- invoking the new JET 4 cascade delete-to-null option.
Sub CreateTableDDL()
'Purpose: Create two tables, their indexes and relation.
Dim cmd As New ADODB.Command
Dim strSql As String
'Initialize
cmd.ActiveConnection = CurrentProject.AccessConnection
'Create the Contractor table.
strSql = "CREATE TABLE tblDdlContractor " & _
"(ContractorID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Surname TEXT(30) WITH COMP NOT NULL, " & _
"FirstName TEXT(20) WITH COMP, " & _
"Inactive YESNO, " & _
"HourlyFee CURRENCY DEFAULT 0, " & _
"PenaltyRate DOUBLE, " & _
"BirthDate DATE, " & _
"Notes MEMO, " & _
"CONSTRAINT FullName UNIQUE (Surname, FirstName));"
cmd.CommandText = strSql
cmd.Execute
Debug.Print "tblDdlContractor created."
'Create the Booking table.
strSql = "CREATE TABLE tblDdlBooking " & _
"(BookingID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"BookingDate DATE CONSTRAINT BookingDate UNIQUE, " & _
"ContractorID LONG REFERENCES tblDdlContractor (ContractorID) " & _
"ON DELETE SET NULL, " & _
"BookingFee CURRENCY, " & _
"BookingNote TEXT (255) WITH COMP NOT NULL);"
cmd.CommandText = strSql
cmd.Execute
Debug.Print "tblDdlBooking created."
End Sub
