Overview of the ADOX Model


The ADOX library supports schema and security tasks . You can use this library to manage objects and thereby modify the architecture of your application's design. With the exception of the Catalog object, all objects in the ADOX library have matching collections. The catalog is the container of the elements within an Access database file. For example, within a Jet database there is a Tables collection of Table objects. You use ADOX collections to add and organize new objects in a catalog. Selected objects ”such as a table, an index, a key, and a column ”have a Properties collection. You use these Properties collections to manage the behavior of the objects within an application. You manage the Users and Groups collections to control permissions for other ADOX objects, such as tables, views, and procedures. Figure 3-1 shows an overview of the ADOX library.

Note  

You must impose a logon requirement before your application can list the members of the Users and Groups collections. Any attempt to process the members of these collections without logging on can generate a run-time error. You can impose a logon requirement by setting a password for the Admin user .

The ADOX library is an extension of the ADODB library. The Jet ADO provider fully supports ADOX. You can use the two libraries together to build applications. For example, you can build Command objects with the ADODB library and then save them as procedures with the ADOX library. The ADOX library enables you to build tables in ADOX programmatically, as well as to search for tables and their elements in a database. For example, you can determine whether a table with a particular name exists in a database, and you can examine the table's columns to discover their names , data types, and membership in keys and indexes. If a table does not exist, you can add it to the database and populate it with values. Alternatively, you can rename or delete an existing table from a database and replace it with a new one. The ability of the ADOX library to define new data structures and modify existing ones makes it a viable alternative to the SQL Data Definition Language.

click to expand
Figure 3.1: The ADOX object library.

The Catalog Object

The Catalog object is the highest-level container in the ADOX library. Its members define the schema and security model for a database. Its ActiveConnection property defines the connection to which the catalog belongs. The Catalog object is the database's container for tables, views, procedures, users, and groups within a connection or database. A Catalog object does not require a name, because there will be only one such object per database. Nevertheless, your application can have multiple catalogs open concurrently, with each pointing to a different database. Use the Catalog object's Create method to open a new database and access its catalog.

You need a Connection object for the catalog so that ADO knows which catalog to make available. You assign the Connection object to the catalog by setting its ActiveConnection property. Once ADO knows which database to reference with a catalog, you have programmatic access to the contents of the catalog. You control access using database and user-level security techniques. While you can reference a table's contents with an ADODB library reference, you must use an ADOX reference to loop through the Columns collection of a table. Only the ADOX library has Tables and Columns collections.

You can use the Catalog object to enumerate the members of any of the collections within it. This chapter will demonstrate techniques for enumerating the members of the Tables collection. In addition, you'll learn how to loop through the elements of tables, such as columns, keys, and indexes.

The Table Object

The Table object is a member of the Tables collection, which is a member of the Catalog object. Each Table object has a Name property and a Type property. A Table object can be a standard table within the current database or a linked table based on ODBC and non-ODBC data sources. A Table object can even be a view. The Type property values also include two system table types: Jet system tables and the Access system tables. Table 3-1 shows these property values.

Table 3.1: ADOX Type Values for the Table Object

Type Value

Description

ACCESS TABLE

An Access system table

LINK

A linked table from a non-ODBC data source

PASS-THROUGH

A linked table through an ODBC data source

SYSTEM TABLE

A Jet system table

TABLE

A table developed by or for your application

VIEW

A virtual table from a nonparameterized query that returns rows

You also are likely to find the ParentCatalog property particularly useful. This property must be set if you plan to invoke the AutoIncrement property for a column. You need the AutoIncrement property to create AutoNumber data types programmatically with ADOX, but you cannot set the AutoIncrement property to True unless you first set the ParentCatalog property to point at the catalog for a column's parent. (A column's parent is simply the table to which the Column object belongs.)

In addition to the Columns collection, a Table object has a Keys collection and an Indexes collection. You probably will use the primary key and foreign key property settings frequently. There is also a unique key property for tables that can benefit from a candidate key. Members of the Indexes collection can speed up some tasks (such as sorting) and enable others (such as using the Seek method to find a subset or records in a recordset).

Note  

A candidate key is a subset of column values that uniquely identifies the rows in a table. When a table has more than one candidate key, a database designer can select any one of them to be the table's primary key.

The Column Object

A Columns collection can belong to tables, keys, and indexes. A Column object is roughly comparable to a Field object in the ADODB library. A column represents a set of data that refers to a specific characteristic of the entity represented by the table. The Column object has several properties:

  • Name    This property is the name of the column.

  • Type     This property indicates the data type of the column. All the data within a column is of the same type.

  • Attributes     This property describes the two possible characteristics of a column: whether the column can contain Nulls and whether it has a fixed length.

  • DefinedSize     This property designates the maximum size in number of characters for entries within the column.

  • Precision and NumericScale     These properties are used exclusively for fields with a decimal data type. Precision represents the maximum total number of digits used to convey a value in the column. NumericScale designates how many digits to the right of the decimal point are available to express a value.

When a Column object is an index or a key, other properties are available too, such as SortOrder and RelatedColumn .

The Index Object

The Index object sets indexes for a table. It has five properties: Name , IndexNulls , PrimaryKey , Unique , and Clustered . With the exception of the Name property, all these properties will be read-only after you append the index. The Name property represents the name of the index. The PrimaryKey , Unique , and Clustered properties are Boolean and indicate , respectively, whether the index is a primary key, unique, or clustered. (An index is described as clustered when the physical order of rows on a storage device matches the indexed order of rows.)

Note  

Jet databases do not support the clustered property for an index. Therefore, the value of this property will always be False for any index in a Jet database.

The IndexNulls property can assume one of three different values. Setting this property to adIndexNullsDisallow , the default setting, causes the Index construction to fail if a Null exists in the column's index. Assigning the adIndexNullsIgnore constant to IndexNulls allows the construction of the index if a Null exists in the index but sets the Ignore Nulls box in the Indexes window to Yes. Using adIndexNullsIgnoreAny also constructs the index even when the index contains a Null , but it sets the Ignore Nulls box in the Indexes window to No. Finally, assigning adIndexNullsAllow permits the entry of Null key column values without consequence.

The Key Object

The Key object embodies the behavior of foreign keys in its properties. Of course, the Name property is the name of the key. The RelatedTable property designates the table to which a foreign key points. The DeleteRule and UpdateRule properties determine what happens when a primary key is deleted or updated. The Type property of the Key object represents the type of key and has three options: adKeyForeign for foreign keys, adKeyPrimary for primary keys, and adKeyUnique for unique keys.




Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net