SQL DDL for mdb / access database

[Vini]

I want to ask about SQL DDL for mdb / access database.
I always create table, create field, rename field, set
field for defalt value/required proprty, create & delete
relationship from form design access.
Now I want to make it all by SQL DDL.
If someone know and expert for this knowledge or have
document about SQL DDL Access, please teach me.
I'm already search in help Access and knowlegde base
Microsoft but I find not yet.

Warm regards.

 

[Allen Browne]

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