Data Definition Language (DDL): DAO

Data Definition Language (DDL)

Data Definition Language (DDL) is a programmatic way to create and modify the database structure, that is, objects like tables, indexes, and relationships.

Before Access came along, DDL statements were the only way to modify the database structure in most relational database management systems. Access introduced the graphical user interface (GUI) to do most DDL functions. Because the Access GUI is so easy to use, most Access users never have reason to use DDL statements. However, there are circumstances under which it is advantageous to use DDL statements.

I use them a lot for automating data import processes. I can create a temporary table, import data to the temp table, change data types, remove indexes from the permanent table, append data from the temp to the permanent table, then rebuild the indexes -- all automatically, all in SQL code.

But that's not the only use. You can also use DDL to set the seed and interval for an autonumber field, to remotely change the structure of a back-end database (useful for multi-user databases), or make any other change to a production database. In a web environment, it can be used to add databases to an active website.

In this three-part series, I'll be addressing both SQL and DAO.

• SQL vs. DAO
• DDL Using SQL
• DDL Using DAO (this post)
• You can download this whole series in a single document with sample database, here: http://www.rogersaccesslibrary.com/forum/topic570_post587.html

 

DDL Using DAO

DAO (Data Access Objects) works differently than SQL. DAO is an object model specific to Access (or rather for the MDB or ACCDB file types). One reason for using DAO over SQL is that you can set Access specific properties (like Default Value and Validation Rules) with it. Also, certain datatypes (like Hyperlink) can only be created with DAO.

In general, with DAO, you:

1. Declare object variables (e.g., Table, Field, Property, etc.)
2. Instantiate the object (that is, create the object)
3. Append it to the appropriate Collections (that is, a table to the Tables Collection)

You can't create a Table without creating at least one Field, so you have to create and append the fields for the table before you append the table. If you set Properties for the field, you must do so before you append the field to the fields collection. So creating a table goes something like this:

1. Declare variables (Table, Field)
2. Instantiate Table
   • Instantiate Field 1
   • Instantiate Property 1
   • Append Property 1
   • Instantiate Property 2
   • Append Property 2
   • Append Field1
   • Instantiate Field 2
     • Instantiate Property 1
     • Append Property 1
   • Append Field 2
3. Append Table

So, specific examples might go something like these:

 

Create Table

Sub exaCreateTableDAO()
'DAO DDL example demonstrates creating a table, fields, properties

' Declare object variables
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb

' Create the table (BooksDAO)
Set tbl = db.CreateTableDef("BooksDAO")

' Create a field (ISBN)
Set fld = tblNew.CreateField("ISBN", dbText, 13)

' Set field properties
fld.Required = True

' Append field (ISBN) to Fields collection
tbl.Fields.Append fld

' Create a field (Title)
Set fld = tblNew.CreateField("ISBN", dbText, 100)

' Set field properties
fld.Required = True
fld.AllowZeroLength = False
fld.DefaultValue = "Unknown"

' Append field (Title) to Fields collection
tbl.Fields.Append fld

' Append table (BooksDAO) to TableDef collection
db.TableDefs.Append tbl

End Sub

 

Modify Table

Modifying a table (that is, adding a new field or adding new properties to an existing field) is similar, except instead of instantiating a new table, you instantiate an existing table. When modifying an existing table or field, you do not need to append it to its collection.

Sub exaModifyTable()
'DAO DDL example'demonstrates modifying a table by adding a field
' and modifying an existing field's property

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb

' Instantiate existing table (BooksDAO)
Set tdf = db.TableDefs("BooksDAO")

' Create a field (Price)
Set fld = tdf.CreateField("Price", dbCurrency)

' Append field to Fields collection
tdf.Fields.Append fld

' Modify existing field (Title) with properties (validation rule
' and validation text)
Set fld = tdf.Fields("Title")
fld.ValidationRule = "Like 'A*' or Like 'Unknown'"
fld.ValidationText = "Known value must begin with A"

End Sub

 

Modify Field Names

Sub exaModifyFieldNames()
'DAO DDL example demonstrates modifying field names

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("MyTable")
For Each fld In tdf.Fields
    fld.Name = fld.Name & "new"
Next

End Sub

Create Database

Sub exaCreateDB()
'DAO DDL example demonstrates creating a database programmatically

Dim dbNew As DAO.Database
Set dbNew = CreateDatabase _
("C:\classes\cis253\winter98\MoreBks", dbLangGeneral)

End Sub

Delete Table

Sub DeleteTable()
'DAO DDL example demonstrates deleting a table

Dim db As DAO.Database
Set db = CurrentDb
db.TableDefs.Delete "tstBooks"

End Sub

Create Index

Sub exaCreateIndex()
'DAO DDL example demonstrates creating an index

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs!Books

' Create index
Set idx = tdf.CreateIndex("PriceTitle")

' Append fields to index
Set fld = idx.CreateField("Price")
idx.Fields.Append fld
Set fld = idx.CreateField("Title")
idx.Fields.Append fld

' Append index to table
tdf.Indexes.Append idx

End Sub

 

Create Primary Key

Sub CreatePrimaryKey()
'DAO DDL example demonstrates creating an index

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs!Books

' Create index
Set idx = tdf.CreateIndex("PriceTitle")

' Append fields to index
Set fld = idx.CreateField("Price")
idx.Fields.Append fld
Set fld = idx.CreateField("Title")
idx.Fields.Append fld

' Make Index primary
idx.Primary = True

' Append index to table
tdf.Indexes.Append idx

End Sub

 

Delete Index

Sub DeleteIndex()
'DAO DDL example demonstrates creating a composite primary key

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
Set tdf = db.TableDefs!Books
tdf.Indexes.Delete "PriceTitle"

End Sub

 

Create Relationship

Sub exaRelations()

'DAO DDL example creating a Relationship

Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb

' Create relation
Set rel = db.CreateRelation("PublisherRegions", _
"PUBLISHERDAO", "SALESREGIONS")

' Set referential integrity w/ cascade updates
rel.Attributes = dbRelationUpdateCascade

' Specify key field in KeyTable (Publishers)
Set fld = rel.CreateField("PubID")

' Specify foreign key in ForeignTable (SalesRegions)
fld.ForeignName = "PubIDFK"

' Append field to Relation
rel.Fields.Append fld

' Append relation to Relations collection
db.Relations.Append rel

End Sub

 

Drop Relationship

Sub exaDeleteRelation()

'DAO DDL example Deleting a Relationship
Dim db As Database
Set db = CurrentDb
db.Relations.Delete "PublisherRegions"

End Sub

 

Create Table With AutoNumber PrimaryKey And Hyperlink

Sub exaCreateTableWithAutoNumberAndHyperlink()

'DAO DDL example demonstrates creating a table, fields, properties
Dim db As DAO.Database
Dim tblNew As DAO.TableDef
Dim fld As DAO.Field

' Create the table and a field
Set db = CurrentDb()
Set tblNew = db.CreateTableDef("NewTable")
Set fld = tblNew.CreateField("AutoField", dbLong)

' Set field properties
fld.Required = True
fld.Attributes = dbAutoIncrField

' Append field to Fields collection
tblNew.Fields.Append fld

' Create Primary Key
Set idx = tblNew.CreateIndex("PrimaryKey")

' Append fields to index
Set fld = idx.CreateField("AutoField")
idx.Fields.Append fld

' Make Index primary
idx.Primary = True

' Append index to table
tblNew.Indexes.Append idx

' Create hyperlink field
Set fld = tblNew.CreateField("HyperField", dbMemo)

' Set field properties
fld.Attributes = dbHyperlinkField

' Append field to Fields collection
tblNew.Fields.Append fld

' Append table to TableDef collection
db.TableDefs.Append tblNew

End Sub