A database contains a number of tables, indexes, constraints, and so on. Collectively the definition of these items is known as the database's schema. Both DAO and ADO offer an easy, object-based way to both create new objects in the database as well as to retrieve information about the definition of existing objects.
Before tables or other objects can be defined, the database itself must be created. The following code creates and opens a new Microsoft Jet database.
DAO
Sub DAOCreateDatabase()
Dim db As DAO.Database
Set db = DBEngine.CreateDatabase(".\New.mdb", dbLangGeneral)
End Sub
ADOX
Sub ADOCreateDatabase()
Dim cat As New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\New.mdb;"
End Sub
As discussed earlier in this article (see the section "ADOX: Data Definition and Security"), the DAO Database object corresponds to the Catalog object in ADOX. So, to create a new Jet database using ADOX, you use the Catalog objects Create method.
In the preceding DAO code the Locale parameter is specified as dbLangGeneral. In the ADOX code, locale is not explicitly specified. The default locale for the Microsoft Jet Provider is equivalent to dbLangGeneral. Use the ADO Locale Identifier property to specify a different locale.
In DAO CreateDatabase also can take a third Options parameter, specifying information for encryption and database version. For example, the following line is used to create an encrypted, version 1.1 Microsoft Jet database:
Set db = DBEngine.CreateDatabase(".\New.mdb", dbLangGeneral, _
dbEncrypt Or dbVersion11)
In ADO encryption and database version information is specified by provider-specific properties. With the Microsoft Jet Provider, use the Encrypt Database and Engine Type properties, respectively. The following line of code specifies these values in the connection string to create an encrypted, version 1.1 Microsoft Jet database:
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\New.mdb;" & _
"Jet OLEDB:Encrypt Database=True;" & _
"Jet OLEDB:Engine Type=2;"
Both DAO and ADOX contain collections of objects that can be used to retrieve information about the database's schema. By iterating through the collections, it is easy to determine the structure of the objects in the database.
The following code demonstrates how to print the name of every table in the database by looping through the DAO TableDefs collection and the ADOX Tables collection.
DAO
Sub DAOListTables()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Loop through the tables in the database and print their name
For Each tbl In db.TableDefs
Debug.Print tbl.Name
Next
End Sub
ADOX
Sub ADOListTables()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Loop through the tables in the database and print their name
For Each tbl In cat.Tables
If tbl.Type <> "VIEW" Then Debug.Print tbl.Name
Next
End Sub
With DAO the TableDef object represents a table in the database and the TableDefs collection contains a TableDef object for each table in the database. This is similar to ADO, in which the Table object represents a table and the Tables collection contains all the tables.
However, unlike DAO, the ADO Tables collection may contain Table objects that aren't actual tables in your Microsoft Jet database. For example, row-returning, non-parameterized Microsoft Jet queries (considered Views in ADO) are also included in the Tables collection. To determine whether the Table object represents a table in the database, use the Type property. The following table lists the possible values for the Type property when using ADO with the Microsoft Jet Provider.
| Type | Description |
| ACCESS TABLE | The Table is a Microsoft Access system table. |
| LINK | The Table is a linked table from a non-ODBC data source. |
| PASS-THROUGH | The Table is a linked table from an ODBC data source. |
| SYSTEM TABLE | The Table is a Microsoft Jet system table. |
| TABLE | The Table is a table. |
| VIEW | The Table is a row-returning, non-parameterized query. |
In addition to being able to retrieve schema information using collections in ADOX, you can use the ADO OpenSchema method to return a Recordset containing information about the tables in the database. See "Appendix C: Microsoft Jet 4.0 OLE DB Provider-Defined Property Values" for the schema rowsets globally unique identifiers (GUIDs) that are available in ADO when using the Microsoft Jet Provider.
In general, it is faster to use the OpenSchema method rather than looping through the collection, because ADOX must incur the overhead of creating objects for each element in the collection. The following code demonstrates how to use the OpenSchema method to print the same information as the previous DAO and ADOX examples:
Sub ADOListTables2()
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the tables schema rowset
Set rst = cnn.OpenSchema(adSchemaTables)
' Loop through the results and print
' the names in the debug window
Do Until rst.EOF
If rst.Fields("TABLE_TYPE") <> "VIEW" Then
Debug.Print rst.Fields("TABLE_NAME")
End If
rst.MoveNext
Loop
End Sub
Microsoft Jet databases can contain two types of tables. The first type is a local table, in which the definition and data are stored within the database. The second type is a linked table, in which the table resides in an external database, but a link along with a copy of the table's definition is stored in the database.
The following example creates a new local table named "Contacts."
DAO
Sub DAOCreateTable()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create a new TableDef object.
Set tbl = db.CreateTableDef("Contacts")
With tbl
' Create fields and append them to the new TableDef object.
' This must be done before appending the TableDef object to
' the TableDefs collection of the Database.
.Fields.Append .CreateField("ContactName", dbText)
.Fields.Append .CreateField("ContactTitle", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)
.Fields("Notes").Required = False
End With
' Add the new table to the database.
db.TableDefs.Append tbl
db.Close
End Sub
ADOX
Sub ADOCreateTable()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create a new Table object.
With tbl
.Name = "Contacts"
' Create fields and append them to the new Table
' object. This must be done before appending the
' Table object to the Tables collection of the
' Catalog.
.Columns.Append "ContactName", adVarWChar
.Columns.Append "ContactTitle", adVarWChar
.Columns.Append "Phone", adVarWChar
.Columns.Append "Notes", adLongVarWChar
.Columns("Notes").Attributes = adColNullable
End With
' Add the new table to the database.
cat.Tables.Append tbl
Set cat = Nothing
End Sub
The process for creating a table using DAO or ADOX is the same. First, create the object (TableDef or Table), append the columns (Field or Column objects) and, finally, append the table to the collection. Though the process is the same, the syntax is slightly different.
With ADOX it is not necessary to use a "create" method to create the column before appending it to the collection. The Append method can be used to both create and append the column.
You'll also notice the data type names for the columns are different between DAO and ADOX. The following table shows how the DAO data types that apply to Microsoft Jet databases map to the ADO data types.
| DAO data type | ADO data type |
| dbBinary | adBinary |
| dbBoolean | adBoolean |
| dbByte | adUnsignedTinyInt |
| dbCurrency | adCurrency |
| dbDate | adDate |
| dbDecimal | adNumeric |
| dbDouble | adDouble |
| dbGUID | adGUID |
| dbInteger | adSmallInt |
| dbLong | adInteger |
| dbLongBinary | adLongVarBinary |
| dbMemo | adLongVarWChar |
| dbSingle | adSingle |
| dbText | adVarWChar |
Though not shown in this example, there are a number of other attributes of a table or column that you can set when creating the table or column, using the DAO Attributes property. The following table shows how these attributes map to ADO and Microsoft Jet Provider–specific properties.
| DAO TableDef property |
Value | ADOX Table property |
Value |
| Attributes | dbAttachExclusive | Jet OLEDB:Exclusive Link | True |
| Attributes | dbAttachSavePWD | Jet OLEDB:Cache Link Name/Password | True |
| Attributes | dbAttachedTable | Type | "LINK" |
| Attributes | dbAttachedODBC | Type | "PASS-THROUGH" |
| DAO Field property |
Value | ADOX Column property |
Value |
| Attributes | dbAutoIncrField | AutoIncrement | True |
| Attributes | dbFixedField | ColumnAttributes | adColFixed |
| Attributes | dbHyperlinkField | Jet OLEDB:Hyperlink | True |
| Attributes | dbSystemField | No equivalent | n/a |
| Attributes | dbUpdatableField | Attributes (Field Object) | adFldUpdatable |
| Attributes | dbVariableField | ColumnAttributes | Not adColFixed |
Linking (also known as attaching) a table from an external database allows you to read data, update and add data (in most cases), and create queries using the table in the same way as you would with a table native to the database.
With Microsoft Jet you can create links to Microsoft Jet data, ISAM data (Text, FoxPro, dBASE, and so on), and ODBC data. Tables that are linked through ODBC are sometimes called pass-through tables.
The following listings demonstrate how to create a table that is linked to a table in another Microsoft Jet database.
DAO
Sub DAOCreateAttachedJetTable()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create a new TableDef object.
Set tbl = db.CreateTableDef("Authors")
' Set the properties to create the link
tbl.Connect = ";DATABASE=.\Pubs.mdb;pwd=password;"
tbl.SourceTableName = "authors"
' Add the new table to the database.
db.TableDefs.Append tbl
db.Close
End Sub
ADOX
Sub ADOCreateAttachedJetTable()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Set the name and target catalog for the table
tbl.Name = "Authors"
Set tbl.ParentCatalog = cat
' Set the properties to create the link
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = ".\Pubs.mdb"
tbl.Properties("Jet OLEDB:Link Provider String") = ";Pwd=password"
tbl.Properties("Jet OLEDB:Remote Table Name") = "authors"
' Append the table to the collection
cat.Tables.Append tbl
Set cat = Nothing
End Sub
To create a linked table you must specify the external data source and the name of the external table. With DAO the Connect and SourceTableName properties are used to specify this information. With ADOX several Microsoft Jet Provider-specific properties are used to create the link. When referencing the Table object's Properties collection prior to appending the Table to the Tables collection, you must first set the ParentCatalog property. This is necessary so ADOX knows from which OLE DB Provider to receive the property information. See the section "Appendix B: Microsoft Jet 4.0 OLE DB Properties Reference" for more information about the properties that are available in the Table object's Properties collection when using the Microsoft Jet Provider.
With ADOX the Jet OLEDB:Link Datasource property contains only the file and path name for the database. It does not contain the "database=;" prefix nor is it used to specify the database password or other connection options as the Connect property does in DAO. To specify other connection options in ADOX code, use the Jet OLEDB:Link Provider String property. You do not need to set this property unless you need to set extra connection options. In the previous example, if the pubs.mdb was not secured with a database password you could omit the line of code that sets the Jet OLEDB:Link Provider String property.
When used for a user ID or a password, the value for this property follows the syntax for connection strings used for external data. The syntax is given in the Microsoft Jet Database Engine Programmer's Guide. Specifically, you must use "uid=;" and "pwd=;" to set the user ID and password, respectively, and not "User ID=;" or "Password=;".
Notice that when creating an attached table using both DAO and ADOX it is not necessary to create columns on the table. The Microsoft Jet database engine will automatically create the columns based on the definition of the table in the external data source.
This next example shows how to create a table that is linked to a table in an ODBC data source such as a Microsoft SQL Server database.
DAO
Sub DAOCreateAttachedODBCTable()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create a new TableDef object.
Set tbl = db.CreateTableDef("Titles")
' Set the properties to create the link
tbl.Connect = "ODBC;DSN=ADOPubs;UID=sa;PWD=;"
tbl.SourceTableName = "titles"
' Add the new table to the database.
db.TableDefs.Append tbl
db.Close
End Sub
ADOX
Sub ADOCreateAttachedODBCTable()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Set the name and target catalog for the table
tbl.Name = "Titles"
Set tbl.ParentCatalog = cat
' Set the properties to create the link
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Provider String") = _
"ODBC;DSN=ADOPubs;UID=sa;PWD=;"
tbl.Properties("Jet OLEDB:Remote Table Name") = "titles"
' Append the table to the collection
cat.Tables.Append tbl
Set cat = Nothing
End Sub
Unlike DAO, which has a single Connect property, ADOX with the Microsoft Jet Provider has a separate property that specifies the connection string for tables attached through ODBC. When creating tables attached through ODBC you may want to indicate that the password should be saved as part of the connection string (it is not saved by default). With ADOX, use the Jet OLEDB:Cache Link Name/Password property to indicate that the password should be cached. This is equivalent to setting the dbAttachSavePWD flag in the Table object's Attributes property using DAO.
Once a table is created, you may want to modify it to add or remove columns, change the validation rule, or refresh the link for a linked table.
The following listings demonstrate how to add a new auto-increment column to an existing table.
DAO
Sub DAOCreateAutoIncrColumn()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Get the Contacts table
Set tbl = db.TableDefs("Contacts")
' Create the new auto increment column
Set fld = tbl.CreateField("ContactId", dbLong)
fld.Attributes = dbAutoIncrField
' Add the new table to the database.
tbl.Fields.Append fld
db.Close
End Sub
ADOX
Sub ADOCreateAutoIncrColumn()
Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the new auto increment column
With col
.Name = "ContactId"
.Type = adInteger
Set .ParentCatalog = cat
.Properties("AutoIncrement") = True
End With
' Append the column to the table
cat.Tables("Contacts").Columns.Append col
Set cat = Nothing
End Sub
In the ADOX example, notice that the ParentCatalog property of the Column object is set before the AutoIncrement property in the Properties collection is set to True. In order to access any property in the Properties collection, the Column object must be associated with a provider.
The next example shows how to update an existing linked table to refresh the link. This involves updating the connection string for the table and then resetting the Jet OLEDB:CreateLink property to tell Microsoft Jet to reestablish the link:
DAO
Sub DAORefreshLinks()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
For Each tbl In db.TableDefs
' Check to make sure table is a linked table.
If (tbl.Attributes And dbAttachedTable) Then
tbl.Connect = "MS Access;PWD=NewPassWord;" & _
"DATABASE=.\NewPubs.mdb"
tbl.RefreshLink
End If
Next
End Sub
ADOX
Sub ADORefreshLinks()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
For Each tbl In cat.Tables
' Check to make sure table is a linked table.
If tbl.Type = "LINK" Then
tbl.Properties("Jet OLEDB:Create Link") = False
tbl.Properties("Jet OLEDB:Link Provider String") = _
";pwd=NewPassWord"
tbl.Properties("Jet OLEDB:Link Datasource") = _
".\NewPubs.mdb"
tbl.Properties("Jet OLEDB:Create Link") = True
End If
Next
End Sub
Indexes on a column or columns in a table specify the order of records accessed from database tables and whether 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 index entries will be created when some of the fields in a multicolumn 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, which 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 following table 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 preceding table. 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 multicolumn index contains a Null value.
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.
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"
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"
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, and dbRelationRight.
| DAO Relation Object property |
Value | ADOX Key Object property |
Value |
| Attributes | dbRelationUnique | Type | adKeyUnique |
| Attributes | dbRelationUpdateCascade | UpdateRule | adRICascade |
| Attributes | dbRelationDeleteCascade | DeleteRule | adRICascade |
As discussed in the section "Executing Queries," the ADO Command object is similar to the DAO QueryDef object in that it specifies an SQL string and parameters and executes the query. However, unlike the DAO QueryDef object, the ADO Command object cannot be used directly to persist a query. By specifying a name for the QueryDef when it is created, the DAO QueryDef is automatically appended to the QueryDefs collection and persisted in the database. This differs from ADO, in which all Command objects are temporary queries. You must explicitly append the Command to the ADOX Procedures or Views collection in order to persist it in the database.
The Microsoft Jet Provider defines Microsoft Jet queries as Views if the query is a row-returning, non-parameterized query. The provider defines a procedure as either a non-row-returning query (a bulk operation) or a parameterized row-returning query.
The following listings demonstrate how to create a row-returning, non-parameterized query.
DAO
Sub DAOCreateQuery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create query
Set qry = db.CreateQueryDef("AllCategories", _
"SELECT * FROM Categories")
db.Close
End Sub
ADOX
Sub ADOCreateQuery()
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the query
cmd.CommandText = "SELECT * FROM Categories"
cat.Views.Append "AllCategories", cmd
Set cat = Nothing
End Sub
In this example, because the SQL statement is a non-parameterized, row-returning query, the ADO Command object is appended to the ADOX Views collection. Note that when using the Microsoft Jet Provider it is possible to append a Command object to either the Views or Procedures collection regardless of the type of query that is being created. However, if a query such as the one in this example is appended to the Procedures collection, and then the Procedures and Views collections are refreshed, you'll notice that the query is no longer in the Procedures collection, but is now in the Views collection.
Likewise, you can append a parameterized query, or a non-row-returning bulk operation query to either the Views or Procedures collection. However, ADOX will actually store these types of queries in the Procedures collection. If you append to the Views collection, then refresh both the Views and Procedures collections, you'll find that the newly appended query is now in the Procedures collection.
The following listings demonstrate how to create a parameterized query and save it in the database.
DAO
Sub DAOCreateParameterizedQuery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create query
Set qry = db.CreateQueryDef("Employees by Region", _
"PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion]")
db.Close
End Sub
ADOX
Sub ADOCreateParameterizedQuery()
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the Command
cmd.CommandText = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion]"
' Create the Procedure
cat.Procedures.Append "Employees by Region", cmd
Set cat = Nothing
End Sub
The code for creating a parameterized query is very similar using DAO and ADOX. Note, however, that although the ADO Command object allows you to create parameters using the CreateParameter method, this information will not be saved when creating or updating a Procedure. You must specify the parameters as part of the SQL string.
Also note that Microsoft Jet will interpret the SQL statement differently when a query is created with ADOX and the Microsoft Jet Provider rather than DAO. The Microsoft Jet Provider always sets a Microsoft Jet database engine option for American National Standards Institute (ANSI) compliance. This may cause differences in behavior between DAO and ADO when creating or executing queries. For example, if the SQL statement in the previous code had been written as follows:
"PARAMETERS [prmRegion] TEXT;" & _
"SELECT * FROM Employees WHERE Region = [prmRegion]"
omitting the (255) after the Text keyword, the parameter would be created as a Text field (dbText, adVarWChar) when using DAO, but as a Memo field (dbMemo, adLongVarWChar) when using ADO.
Further, some SQL statements that execute when using DAO will fail to execute when using ADO due to additional reserved words. For a list of reserved words, see "Appendix D: Microsoft Jet 4.0 ANSI Reserved Words."
The following listings demonstrate how to modify an existing query.
DAO
Sub DAOModifyQuery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Get the query
Set qry = db.QueryDefs("Employees by Region")
' Update the SQL and save the updated query
qry.SQL = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
db.Close
End Sub
ADO
Sub ADOModifyQuery()
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Get the query
Set cmd = cat.Procedures("Employees by Region").Command
' Update the SQL
cmd.CommandText = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
' Save the updated query
Set cat.Procedures("Employees by Region").Command = cmd
Set cat = Nothing
End Sub
In the ADO code setting the Procedure object's Command property to the modified Command object saves the changes. If this last step were not included, the changes would not have been persisted to the database. This difference results from the fact that ADO Command objects are designed as temporary queries, while DAO QueryDef objects are designed as saved queries. You need to be aware of this when working with Commands, Procedures, and Views. You may think that the following ADO code examples are equivalent:
Set cmd = cat.Procedures("Employees by Region").Command
cmd.CommandText = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
Set cat.Procedures("Employees by Region").Command = cmd
and:
cat.Procedures("Employees by Region").CommandText = _
"PARAMETERS [prmRegion] TEXT;" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
However, they are not. Both will compile, but the second piece of code will not actually update the query in the database. In the second example ADOX will create a tear-off command object and hand it back to Visual Basic for Applications. Visual Basic for Applications will then ask ADOX to update the CommandText property, which it does. Finally, Visual Basic for Applications moves to execute the next line of code and the Command object is lost. ADOX is never asked to update the Procedure with the changes to the modified Command object.
SQL pass-through queries are SQL statements that are sent directly to the database server without interpretation by the Microsoft Jet database engine. When creating an SQL pass-through query, you must specify the SQL statement to execute as well as an ODBC connection string.
With DAO pass-through queries provide a means of improving performance when accessing external ODBC data. With ADO it is not necessary to create SQL pass-through queries in your Microsoft Jet database in order to have good performance when accessing external data. With ADO you can use the Microsoft OLE DB Provider for SQL Server to directly access SQL Server without the overhead of Microsoft Jet or ODBC. You can also use the Microsoft OLE DB Provider for ODBC to access data in any ODBC data source.
While it is no longer necessary to create SQL pass-through queries in your Microsoft Jet database, it is still possible to do so using ADOX and the Microsoft Jet Provider. The following code demonstrates how to create an SQL pass-through query.
DAO
Sub DAOCreateSQLPassThrough()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create query
Set qry = db.CreateQueryDef("Business Books", _
"SELECT * FROM Titles WHERE Type = 'business'")
qry.Connect = "ODBC;DSN=ADOPubs;UID=sa;PWD=;"
qry.ReturnsRecords = True
db.Close
End Sub
ADOX
Sub ADOCreateSQLPassThrough()
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the Command
Set cmd.ActiveConnection = cat.ActiveConnection
cmd.CommandText = "SELECT * FROM Titles WHERE Type = 'business'"
cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
cmd.Properties("Jet OLEDB:Pass Through Query Connect String") = _
"ODBC;DSN=ADOPubs;database=pubs;UID=sa;PWD=;"
' Create the Procedure
cat.Procedures.Append "Business Books", cmd
Set cat = Nothing
End Sub