Indexes on a column or columns in a table
specify the order of records accessed from database tables and whether or not
duplicate records are accepted. The following code creates an index on the
Country field of the Employees table.
DAO
Sub DAOCreateIndex()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim idx As DAO.Index
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
Set tbl = db.TableDefs("Employees")
' Create Index object append Field object to the Index object.
Set idx = tbl.CreateIndex("CountryIndex")
idx.Fields.Append idx.CreateField("Country")
' Append the Index object to the
' Indexes collection of the TableDef.
tbl.Indexes.Append idx
db.Close
End Sub
ADOX
Sub ADOCreateIndex()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim idx As New ADOX.Index
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
Set tbl = cat.Tables("Employees")
' Create Index object append table columns to it.
idx.Name = "CountryIndex"
idx.Columns.Append "Country"
' Allow Null values to be added in the index field
idx.IndexNulls = adIndexNullsAllow
' Append the Index object to the Indexes collection of Table
tbl.Indexes.Append idx
Set cat = Nothing
End Sub
The process for creating an index is the same in
ADO and DAO. Create the index, append columns to the index, and then append the
index to the table. However, there are some differences in behavior between the
Index objects in these two models. DAO has two properties,
Required and IgnoreNulls, that
together determine whether or not Null values can be inserted for fields in the
index and whether or not index entries will be created when some of the fields
in a multi-column index contain Null. By default, both of these properties are
False, indicating that Null values are allowed in the index and that an index
entry will be added. This differs from ADO, which has a single property, IndexNulls for this purpose. By default, the IndexNulls property is adIndexNullsDisallow that
indicates that Null values are not allowed in the index and that no index entry
will be added if a field in the index contains Null.
The table below shows the mapping between the
DAO Required and IgnoreNulls
properties to the ADOX IndexNulls property.
DAO Required
|
DAO IgnoreNulls
|
ADOX IndexNulls
|
Description
|
True
|
False
|
adIndexNullsDisallow
|
A Null value isn't allowed in the index field;
no index entry added.
|
False
|
True
|
adIndexNullsIgnore
|
A Null value is allowed in the index field; no
index entry added.
|
False
|
False
|
adIndexNullsAllow
|
A Null value is allowed in the index field;
index entry added.
|
Note that ADO defines an additional value for
the IndexNulls property, adIndexNullsIgnoreAny,
that is not listed in the table above. The Microsoft Jet Provider does not
support this type of index. Setting IgnoreNulls to adIndexNullsIgnoreAny when using the Microsoft Jet Provider
will result in a run-time error. The purpose of adIndexNullsIgnoreAny,
if it was to be supported by a provider, is to ignore an entry if any column of
a multi-column index contains a Null value.
Defining Keys and Relationships
Once the structure of a table has been defined,
it is useful to define keys for the table and the relationships between tables.
Microsoft Jet will use the information provided in the key and relationship
definitions to optimize queries
Creating a Primary Key
A table often has a column or combination of
columns whose values uniquely identify a row in a table. This column (or
combination of columns) is called the primary key of
the table. When you define a primary key, the Microsoft Jet database engine
will create an index to enforce the uniqueness of the key.
Using the Contacts table created in previous
examples, the following listings demonstrate how to make the ContactId column
the primary key.
DAO
Sub DAOCreatePrimaryKey()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim idx As DAO.Index
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
Set tbl = db.TableDefs("Contacts")
' Create the Primary Key and append table columns to it.
Set idx = tbl.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append idx.CreateField("ContactId")
' Append the Index object to the
' Indexes collection of the TableDef.
tbl.Indexes.Append idx
db.Close
End Sub
ADOX
Sub ADOCreatePrimaryKey()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim pk As New ADOX.Key
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
Set tbl = cat.Tables("Contacts")
' Create the Primary Key and append table columns to it.
pk.Name = "PrimaryKey"
pk.Type = adKeyPrimary
pk.Columns.Append "ContactId"
' Append the Key object to the Keys collection of Table
tbl.Keys.Append pk
Set cat = Nothing
End Sub
With DAO, the Index
object is used to create primary keys. The key is created much like any other
index except that the Primary property is set to True.
ADO, however, has a Key object that is used to create
new keys. The steps in creating a key are similar to creating an index.
However, when creating a Key, you must specify the type
of Key you want to create. In this case, the key type
is adKeyPrimary which indicates that you want to create
a primary key.
Alternatively, the ADOX code to create and
append the key could have been written in a single line of code. The following
code:
' Create the Primary Key and append table columns to it.
pk.Name = "PrimaryKey"
pk.Type = adKeyPrimary
pk.Columns.Append "ContactId"
' Append the Key object to the Keys collection of Table
tbl.Keys.Append pk
is equivalent to:
' Append the Key object to the Keys collection of Table
tbl.Keys.Append "PrimaryKey", adKeyPrimary, "ContactId"
Creating One-to-Many Relationships (Foreign Keys)
One-to-many relationships between tables (where
the primary key value in the primary table may appear in multiple rows in the
foreign table) are established by creating foreign keys. A foreign key is a column
or combination of columns whose values match the primary key of another table.
Unlike a primary key, a foreign key does not have to be unique.
DAO
Sub DAOCreateForeignKey()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' This key already exists in the Northwind database.
' For the purposes of this example, we're going to
' delete it and then recreate it
db.Relations.Delete "CategoriesProducts"
' Create the relation
Set rel = db.CreateRelation()
rel.Name = "CategoriesProducts"
rel.Table = "Categories"
rel.ForeignTable = "Products"
' Create the field the tables are related on
Set fld = rel.CreateField("CategoryId")
' Set ForeignName property of the field to the name of
' the corresponding field in the primary table
fld.ForeignName = "CategoryId"
rel.Fields.Append fld
' Append the relation to the collection
db.Relations.Append rel
End Sub
ADOX
Sub ADOCreateForeignKey()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim fk As New ADOX.Key
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Get the table for the foreign side of the relationship
Set tbl = cat.Tables("Products")
' This key already exists in the Northwind database.
' For the purposes of this example, we're going to
' delete it and then recreate it
tbl.Keys.Delete "CategoriesProducts"
' Create the Foreign Key
fk.Name = "CategoriesProducts"
fk.Type = adKeyForeign
fk.RelatedTable = "Categories"
' Append column(s) in the foreign table to it
fk.Columns.Append "CategoryId"
' Set RelatedColumn property to the name of the corresponding
' column in the primary table
fk.Columns("CategoryId").RelatedColumn = "CategoryId"
' Append the Key object to the Keys collection of Table
tbl.Keys.Append fk
Set cat = Nothing
End Sub
Alternatively, the ADOX code to create and
append the key could have been written in a single line of code. The following
code:
' Create the Foreign Key
fk.Name = "CategoriesProducts"
fk.Type = adKeyForeign
fk.RelatedTable = "Categories"
' Append column(s) in the foreign table to it
fk.Columns.Append "CategoryId"
' Set RelatedColumn property to the name of the corresponding
' column in the primary table
fk.Columns("CategoryId").RelatedColumn = "CategoryId"
' Append the Key object to the Keys collection of Table
tbl.Keys.Append fk
is equivalent to:
' Append the Key object to the Keys collection of Table
tbl.Keys.Append "CategoriesProducts", adKeyForeign, _
"CategoryId", "Categories", "CategoryId"
Enforcing Referential Integrity
Referential integrity preserves the defined
relationships between tables when records are added, updated, or deleted.
Maintaining referential integrity within your database requires that there be
no references to nonexistent values, and that if a key value changes, all
references to it change consistently throughout the database.
When you enforce referential integrity users are
prevented from adding new records to a related table when there is no
associated record in the primary table, changing primary key values that would
result in "orphaned" records in the related table, or deleting
records in the primary table when there are associated records in the related
table.
By default, Microsoft Jet enforces relationships
created by DAO or ADOX. A trappable error will occur if you make changes that
violate referential integrity. When defining a new relationship, you can also
specify that Microsoft Jet should cascade updates or deletes. With cascading
updates, when a change is made to the primary key in a record in the primary
table, Microsoft Jet will automatically update the foreign key in all related
records in the related foreign table or tables. Similarly with cascading
deletes, when a record is deleted from the primary table, Microsoft Jet will
automatically delete all related records in the related foreign table or
tables.
In the following example, the code from the
preceding section is modified to create a foreign key that supports cascading
updates and deletes.
DAO
Sub DAOCreateForeignKeyCascade()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' This key already exists in the Northwind database.
' For the purposes of this example, we're going to
' delete it and then recreate it
db.Relations.Delete "CategoriesProducts"
' Create the relation
Set rel = db.CreateRelation()
rel.Name = "CategoriesProducts"
rel.Table = "Categories"
rel.ForeignTable = "Products"
' Specify cascading updates and deletes
rel.Attributes = dbRelationUpdateCascade Or _
dbRelationDeleteCascade
' Create the field the tables are related on
Set fld = rel.CreateField("CategoryId")
' Set ForeignName property of the field to the name of
' the corresponding field in the primary table
fld.ForeignName = "CategoryId"
rel.Fields.Append fld
' Append the relation to the collection
db.Relations.Append rel
End Sub
ADOX
Sub ADOCreateForeignKeyCascade()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim fk As New ADOX.Key
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Get the table for the foreign side of the relationship
Set tbl = cat.Tables("Products")
' This key already exists in the Northwind database.
' For the purposes of this example, we're going to
' delete it and then recreate it
tbl.Keys.Delete "CategoriesProducts"
' Create the Foreign Key
fk.Name = "CategoriesProducts"
fk.Type = adKeyForeign
fk.RelatedTable = "Categories"
' Specify cascading updates and deletes
fk.UpdateRule = adRICascade
fk.DeleteRule = adRICascade
' Append column(s) in the foreign table to it
fk.Columns.Append "CategoryId"
' Set RelatedColumn property to the name of the corresponding
' column in the primary table
fk.Columns("CategoryId").RelatedColumn = "CategoryId"
' Append the Key object to the Keys collection of Table
tbl.Keys.Append fk
Set cat = Nothing
End Sub
The following table shows how the values for the
DAO Attributes property of a Relation
object map to properties of the ADOX Key object.
Note The following values
for the DAO Attributes property of a Relation
object have no corresponding properties in ADOX: dbRelationDontEnforce,
dbRelationInherited, dbRelationLeft,
dbRelationRight.
DAO Relation Object Property
|
Value
|
ADOX Key Object Property
|
Value
|
Attributes
|
dbRelationUnique
|
Type
|
adKeyUnique
|
Attributes
|
dbRelationUpdateCascade
|
UpdateRule
|
adRICascade
|
Attributes
|
dbRelationDeleteCascade
|
DeleteRule
|
adRICascade
|
EmoticonEmoticon