The code example consists of 3 procedures:
1. CreateTableDAO creates a pair of tables, demonstrating different field
types and sizes, including AutoNumber.
2. CreateIndexesDAO creates indexes on the first table, demonstrating a
primary key index, and single-field and multi-field indexes.
3. CreateRelationDAO creates a relation between the 2 tables, with enforced
referential integrity and cascading updates and deletes.
Hopefully that example will let you do everything you need. It uses DAO. If
you need to compare the DAO constands to the names in the interface or names
used in Data Definiton Language or ADO Extension, see:
Field type names (JET, DDL, DAO and ADOX)
at:
http://allenbrowne.com/ser-49.html
------------------------------------code
starts--------------------------------
Sub CreateTableDAO()
'Purpose: Create two tables using DAO.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
'Initialize the Contractor table.
Set db = CurrentDb()
Set tdf = db.CreateTableDef("tblDaoContractor")
'Specify the fields.
With tdf
'AutoNumber: Long with the attribute set.
Set fld = .CreateField("ContractorID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld
'Text field: maximum 30 characters, and required.
Set fld = .CreateField("Surname", dbText, 30)
fld.Required = True
.Fields.Append fld
'Text field: maximum 20 characters.
.Fields.Append .CreateField("FirstName", dbText, 20)
'Yes/No field.
.Fields.Append .CreateField("Inactive", dbBoolean)
'Currency field.
.Fields.Append .CreateField("HourlyFee", dbCurrency)
'Number field.
.Fields.Append .CreateField("PenaltyRate", dbDouble)
'Date/Time field with validation rule.
Set fld = .CreateField("BirthDate", dbDate)
fld.ValidationRule = "Is Null Or <=Date()"
fld.ValidationText = "Birth date cannot be future."
.Fields.Append fld
'Memo field.
.Fields.Append .CreateField("Notes", dbMemo)
'Hyperlink field: memo with the attribute set.
Set fld = .CreateField("Web", dbMemo)
fld.Attributes = dbHyperlinkField + dbVariableField
.Fields.Append fld
End With
'Save the Contractor table.
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing
Debug.Print "tblDaoContractor created."
'Initialize the Booking table
Set tdf = db.CreateTableDef("tblDaoBooking")
With tdf
'Autonumber
Set fld = .CreateField("BookingID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld
'BookingDate
.Fields.Append .CreateField("BookingDate", dbDate)
'ContractorID
.Fields.Append .CreateField("ContractorID", dbLong)
'BookingFee
.Fields.Append .CreateField("BookingFee", dbCurrency)
'BookingNote: Required.
Set fld = .CreateField("BookingNote", dbText, 255)
fld.Required = True
.Fields.Append fld
End With
'Save the Booking table.
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing
Debug.Print "tblDaoBooking created."
'Clean up
Application.RefreshDatabaseWindow 'Show the changes
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub
Sub CreateIndexesDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("tblDaoContractor")
'1. Primary key index.
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ContractorID")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind
'2. Single-field index.
Set ind = tdf.CreateIndex("Inactive")
ind.Fields.Append ind.CreateField("Inactive")
tdf.Indexes.Append ind
'3. Multi-field index.
Set ind = tdf.CreateIndex("FullName")
With ind
.Fields.Append .CreateField("Surname")
.Fields.Append .CreateField("FirstName")
End With
tdf.Indexes.Append ind
'Refresh the display of this collection.
tdf.Indexes.Refresh
'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
Debug.Print "tblDaoContractor indexes created."
End Sub
Sub CreateRelationDAO()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
'Initialize
Set db = CurrentDb()
'Create a new relation.
Set rel = db.CreateRelation("tblDaoContractortblDaoBooking")
'Define its properties.
With rel
'Specify the primary table.
.Table = "tblDaoContractor"
'Specify the related table.
.ForeignTable = "tblDaoBooking"
'Specify attributes for cascading updates and deletes.
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
'Add the fields to the relation.
'Field name in primary table.
Set fld = .CreateField("ContractorID")
'Field name in related table.
fld.ForeignName = "ContractorID"
'Append the field.
.Fields.Append fld
'Repeat for other fields if a multi-field relation.
End With
'Save the newly defined relation to the Relations collection.
db.Relations.Append rel
'Clean up
Set fld = Nothing
Set rel = Nothing
Set db = Nothing
Debug.Print "Relation created."
End Sub
------------------------------------code
ends--------------------------------
