ADO - ADOX Table Object for Table Definition Management

The ADOX Table Object is a part of ADOX (ADO Extensions for Data Definition Language and Security). It is used to work with the structure and definition of database tables rather than only manipulating the data stored inside them. Through the ADOX Table object, developers can create new tables, examine existing tables, modify table properties, and manage the columns and keys associated with a table.

ADOX is especially useful when an application needs to perform database structure-related operations programmatically. Traditional ADO is primarily focused on connecting to databases and working with records, while ADOX extends ADO to provide features for managing database objects such as tables, columns, indexes, keys, views, and procedures.

What Is the ADOX Table Object?

A Table object represents a table in a database. It provides access to information about the table and its associated objects, particularly its columns, indexes, and keys.

For example, suppose a database contains a table named Students:

Students
--------------------------------
StudentID
StudentName
Age
Course

Using the ADOX Table object, an application can represent this table and examine its structure. It can determine the table name and work with the collection of columns belonging to that table.

The Table object is commonly used together with the ADOX Catalog object. The Catalog represents the database structure, while the Table object represents an individual table within that database.

Creating a Table Object

In classic ADOX programming, the Table object can be created and configured before adding it to the database's Tables collection.

A typical VBScript example is:

Dim cat
Dim tbl

Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\College.accdb"

Set tbl = CreateObject("ADOX.Table")

tbl.Name = "Students"

cat.Tables.Append tbl

Here, the Catalog object represents the database, while the Table object represents the new Students table. The Append method adds the table to the database.

The exact provider and connection string depend on the database being used.

Important Properties of the Table Object

The Table object provides properties that describe the table.

Name

The Name property specifies the name of the table.

tbl.Name = "Students"

This property is particularly important when creating a new table because the table must have an appropriate name before it is added to the database.

For an existing table, the Name property can be used to identify which table is being examined.

Type

The Type property describes the type of database object represented by the Table object.

Depending on the provider, a table may represent different types of database objects, such as a regular table or another supported table-like object.

Provider support can vary, so applications should not assume that every database exposes identical table types.

Columns Collection

One of the most important features of the ADOX Table object is its Columns collection.

A table consists of columns, and each column defines a particular piece of information stored in the table.

For example:

Students
--------------------------------
StudentID
StudentName
Age
Course

The Students Table object contains a Columns collection containing:

StudentID
StudentName
Age
Course

An application can examine these columns programmatically.

For example:

Dim col

For Each col In tbl.Columns
    WScript.Echo col.Name
Next

This can be useful when an application needs to discover the structure of a database dynamically.

Adding Columns to a Table

ADOX allows developers to define columns and add them to a table.

For example:

Dim col

Set col = CreateObject("ADOX.Column")

col.Name = "StudentName"
col.Type = 202

tbl.Columns.Append col

The numeric data type value depends on the ADO data-type enumeration being used. In production applications, using the appropriate ADO constant is generally clearer than using a numeric value directly.

A complete table definition may involve creating several Column objects and appending them to the Table object's Columns collection.

Example of Creating a Table with Columns

A more complete example can look like this:

Dim cat
Dim tbl
Dim col

Set cat = CreateObject("ADOX.Catalog")

cat.ActiveConnection = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Data\College.accdb"

Set tbl = CreateObject("ADOX.Table")

tbl.Name = "Students"

Set col = CreateObject("ADOX.Column")
col.Name = "StudentID"
col.Type = 3

tbl.Columns.Append col

Set col = CreateObject("ADOX.Column")
col.Name = "StudentName"
col.Type = 202
col.DefinedSize = 100

tbl.Columns.Append col

Set col = CreateObject("ADOX.Column")
col.Name = "Age"
col.Type = 3

tbl.Columns.Append col

cat.Tables.Append tbl

The example demonstrates the basic relationship:

Catalog
   |
   +-- Table
        |
        +-- Column
        +-- Column
        +-- Column

The exact data-type constants and provider behavior can differ between database systems.

Working with Existing Tables

The Table object can also be used to inspect tables that already exist in a database.

For example:

Dim tbl

For Each tbl In cat.Tables
    WScript.Echo tbl.Name
Next

This retrieves the tables exposed by the database provider.

An application can then examine the columns belonging to a particular table:

Dim tbl
Dim col

Set tbl = cat.Tables("Students")

For Each col In tbl.Columns
    WScript.Echo col.Name
Next

This approach is useful for database administration tools, schema inspection utilities, and applications that need to understand database structures dynamically.

Relationship with the Catalog Object

The ADOX Catalog and Table objects work closely together.

The Catalog object represents the overall database structure.

The Table object represents an individual table within that structure.

The relationship can be understood as:

ADOX Catalog
      |
      +-- Tables Collection
              |
              +-- Table: Students
              |       |
              |       +-- Columns
              |       +-- Indexes
              |       +-- Keys
              |
              +-- Table: Courses
                      |
                      +-- Columns
                      +-- Indexes
                      +-- Keys

This structure makes it possible to navigate through a database schema programmatically.

Table Object and Database Design

The ADOX Table object is useful when applications need to create or inspect database structures during runtime.

For example, an application could create a database table for storing:

Employee
--------------------------------
EmployeeID
EmployeeName
Department
Salary
JoiningDate

The application can define the table and its columns programmatically instead of requiring the developer to manually create the table through a database management interface.

This can be useful in software installation processes, database initialization routines, testing environments, and administrative utilities.

Table Object Versus Recordset

The Table object and Recordset object serve different purposes.

A Table object deals primarily with the structure of a database table.

A Recordset object deals with records and data retrieved from a database.

For example:

ADOX Table
    |
    +-- Table structure
    +-- Columns
    +-- Keys
    +-- Indexes

ADO Recordset
    |
    +-- Rows
    +-- Fields
    +-- Current record
    +-- Data manipulation

If an application wants to inspect what columns a table contains, the ADOX Table object is appropriate. If it wants to retrieve and process the rows stored in that table, a Recordset is generally more appropriate.

Advantages of the ADOX Table Object

The ADOX Table object provides several useful capabilities:

Programmatic table creation
Applications can create database tables without manually using a database management tool.
Schema inspection
Applications can examine existing table definitions and discover their columns.
Database automation
Database structures can be created as part of an application's setup or initialization process.
Dynamic database applications
Applications that need to work with changing database structures can inspect tables at runtime.
Integration with other ADOX objects
Tables can be associated with columns, indexes, and keys to represent a more complete database structure.

Limitations

ADOX is an older technology, and its capabilities depend significantly on the database provider. Different database systems may expose different schema features through ADOX.

Some modern database features may not be fully represented or managed through ADOX. Provider-specific behavior can also affect operations such as creating columns, indexes, keys, or tables.

For newer applications, database-specific APIs, ORM frameworks, or modern data-access libraries may be preferable depending on the development environment.

Practical Use Case

Consider a school-management application that needs to initialize its database when it is installed.

The application can use ADOX to:

1. Connect to the database
2. Create a Students table
3. Define StudentID
4. Define StudentName
5. Define Course
6. Define other required columns
7. Add the table to the database

After the structure has been created, normal ADO data-access functionality can be used to insert, retrieve, update, and delete student records.

Thus, ADOX can handle the database structure, while ADO can handle the data operations.

Conclusion

The ADOX Table Object represents an individual database table and provides programmatic access to its definition. It works primarily with the structure of tables and can be used to create tables, inspect existing tables, and work with their associated columns, indexes, and keys.

Its relationship with the Catalog object is particularly important: the Catalog represents the database as a whole, while the Table object represents a specific table within that database. Understanding this distinction helps developers use ADOX effectively for database schema management and programmatic database administration.