ADO - ADO Connection OpenSchema Method

The OpenSchema method in ActiveX Data Objects (ADO) is used to retrieve information about the structure and metadata of a database. Instead of retrieving the actual data stored in tables, OpenSchema allows an application to ask the database provider questions about its structure. For example, it can be used to find the names of tables, columns, indexes, relationships, views, procedures, and other database objects. This makes OpenSchema particularly useful when an application needs to discover database information dynamically rather than relying on predefined table or column names.

Syntax

The basic syntax of the ADO OpenSchema method is:

Connection.OpenSchema(QueryType, Criteria, SchemaID)

Here, Connection represents an established ADO Connection object.

The QueryType parameter specifies the type of schema information that should be retrieved. ADO provides different schema constants for different types of metadata. For example, adSchemaTables can be used to retrieve information about tables, while adSchemaColumns can be used to retrieve information about columns.

The Criteria parameter is optional and can be used to restrict the information returned by the schema query. For example, an application can specify a particular table name when retrieving column information.

The SchemaID parameter is generally used for provider-specific schema information and is optional in most common situations.

Retrieving Table Information

One of the most common uses of OpenSchema is discovering the tables available in a database.

For example:

Dim rs As ADODB.Recordset

Set rs = conn.OpenSchema(adSchemaTables)

Do Until rs.EOF
    Debug.Print rs!TABLE_NAME
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing

In this example, conn is an already-established ADO Connection object. The adSchemaTables constant tells ADO that information about database tables is required.

The returned information is placed into a Recordset. The application can then move through that Recordset and read fields such as TABLE_NAME.

Depending on the database provider, the returned schema information may also contain details such as the table type, catalog, and schema name.

Retrieving Column Information

OpenSchema can also be used to obtain information about columns.

For example:

Dim rs As ADODB.Recordset

Set rs = conn.OpenSchema(adSchemaColumns)

Do Until rs.EOF
    Debug.Print rs!TABLE_NAME
    Debug.Print rs!COLUMN_NAME
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing

This allows an application to discover the columns present in database tables without executing a normal SELECT query.

Column metadata may include information such as the column name, data type, ordinal position, length, precision, and whether the column permits null values. The exact fields available can vary according to the ADO provider and database system.

Using Criteria

The Criteria argument allows the application to request a smaller and more specific set of metadata.

For example, when working with column information, an application can specify a particular table:

Dim criteria(3) As Variant
Dim rs As ADODB.Recordset

criteria(2) = "Employees"

Set rs = conn.OpenSchema(adSchemaColumns, criteria)

The exact position and meaning of criteria values depend on the particular schema type being requested. Therefore, applications should follow the criteria order documented for the selected schema rowset and database provider.

Using criteria can be more efficient than retrieving metadata for the entire database when only one particular object is required.

Common Schema Types

ADO provides several schema constants that can be passed to OpenSchema. Some commonly encountered examples include:

The availability and behavior of particular schema types depend on the database provider being used.

Example: Displaying Table Names

A complete example can be written as follows:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
         "Data Source=C:\Data\Company.accdb;"

Set rs = conn.OpenSchema(adSchemaTables)

Do While Not rs.EOF

    If rs!TABLE_TYPE = "TABLE" Then
        Debug.Print rs!TABLE_NAME
    End If

    rs.MoveNext
Loop

rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

The program establishes a connection to the database and calls OpenSchema with adSchemaTables. It then examines the returned Recordset and displays objects whose TABLE_TYPE is "TABLE".

The TABLE_TYPE filtering is useful because a schema result may contain different types of database objects, depending on the provider.

Difference Between OpenSchema and a SELECT Query

A normal SQL query is generally used to retrieve application data.

For example:

SELECT EmployeeID, EmployeeName
FROM Employees;

This query retrieves rows stored in the Employees table.

OpenSchema, on the other hand, is primarily concerned with database metadata. It can answer questions such as:

• What tables exist?

• What columns does a table contain?

• What indexes are defined?

• Which columns form a primary key?

• What views are available?

• What procedures are exposed by the provider?

Therefore, OpenSchema is useful when the application needs to understand the database structure itself.

Practical Applications

OpenSchema can be useful in applications that need to work with databases whose structure is not completely known in advance.

For example, a database administration tool can use OpenSchema to display a list of tables automatically. A database browser can use it to show columns and indexes. A code-generation utility can inspect column metadata and generate application classes or forms. A migration or synchronization utility can inspect database structures before performing operations.

It can also be useful for validation. An application can check whether a required table or column exists before attempting to execute an operation against it.

Advantages

The major advantage of OpenSchema is that it provides a standardized ADO mechanism for accessing database metadata.

It can reduce the need to write provider-specific SQL queries for common metadata operations. It also allows applications to discover database structures dynamically, which is valuable for tools that must work with different databases.

Another advantage is that the returned information is represented through a Recordset, making it familiar to developers who already work with ADO.

Limitations

The information returned by OpenSchema depends heavily on the database provider. Different providers may expose different schema types, fields, or levels of metadata support.

Applications should therefore avoid assuming that every provider will return exactly the same schema information. Provider documentation should be consulted when an application depends on a specific metadata field.

Another consideration is that retrieving extensive schema information unnecessarily can add overhead. When possible, applications should use appropriate criteria to retrieve only the metadata they require.

Summary

The ADO OpenSchema method provides a way to retrieve database metadata through an ADO Connection object. Instead of retrieving ordinary application records, it returns information describing the structure and capabilities of the database.

Its general form is:

Connection.OpenSchema(QueryType, Criteria, SchemaID)

By using schema constants such as adSchemaTables, adSchemaColumns, and adSchemaIndexes, applications can discover database objects and their properties dynamically. The resulting information is returned as a Recordset, which can then be examined using normal ADO Recordset operations.

For database exploration tools, administration utilities, dynamic applications, and programs that need to adapt to changing database structures, OpenSchema provides an important mechanism for inspecting database metadata.