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.
Creating a Database
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 document (see the
section titled "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 DAO code above, 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 encrytion 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 + 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;"
Retrieving Schema Information
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 or not 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: Schema Rowsets"
for more information about the schema rowsets 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
Creating and Modifying Tables
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.
Creating Local Tables
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 table below 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
|
EmoticonEmoticon