HOWTO: Use the ADO OpenSchema Method in Visual Basic

ID: Q186246

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0
Microsoft Visual Basic Standard and Enterprise Editions for Windows, version 5.0

SUMMARY

This article describes how to use the OpenSchema method of the ActiveX Data Objects (ADO) Connection object to obtain more information about the database or table.

The syntax for OpenSchema is below:

Set recordset = connection.OpenSchema (QueryType, Criteria, SchemaID)

MORE INFORMATION

Here are the three parameters for the OpenSchema method:

An enumerated value specifying the type of the schema required.

A variant array. The number of elements and the contents in this array depend on the type of schema query to run. You can use this parameter to restrict the number of rows you return in the resultset. However, you cannot limit the number of columns you return using OpenSchema.

Below are the sample parameters for OpenSchema. Note that the criteria changes with the querytype. A list of corresponding criterias for other querytypes is in the online documentation located at the following URL:
http://www.microsoft.com/data/ado/
```
   QueryType        Criteria
   =============================

   adSchemaTables   TABLE_CATALOG
                    TABLE_SCHEMA
                    TABLE_NAME
                    TABLE_TYPE 
```
The ODBC driver for Microsoft Access treats catalog as the database and schema as owner. The ODBC driver for SQL server also follows the same terminology. The criteria Array member indicates the string values to restrict the query results. The number of the array members varies depending on the querytype. For example, TABLE_CATALOG is pubs, TABLE_SCHEMA is "dbo", TABLE_NAME is "authors". Similarly, one has to substitute the column name in case of following querytype adSchemaColumns.
```
   QueryType         Criteria
   ===============================

   adSchemaColumns   TABLE_CATALOG
                     TABLE_SCHEMA
                     TABLE_NAME
                     COLUMN_NAME 
```
You provide the index name in case of adSchemaIndexes querytype.
```
   QueryType          Criteria
   ================================

   adSchemaIndexes    TABLE_CATALOG
                      TABLE_SCHEMA
                      INDEX_NAME
                      TYPE
                      TABLE_NAME 
```
The most important thing to remember is that the order of providing the values has to be the same.

The third parameter varies depending on the provider you use. It is required only if you set the first parameter to adSchemaProviderSpecific; otherwise, it is not used.

The following steps demonstrate the OpenSchema Method.

OpenSchema Method Example

In Visual Basic (VB), select a standard EXE project.

Add three Command buttons to the EXE project.

From the Project menu, choose References. In the References dialog box, select Microsoft ActiveX Objects Library.

This sample uses the Pubs database provided with SQL Server. You need to change the data source name (DSN) to a DSN on your computer. Paste the following code into the GENERAL DECLARATIONS section of the Project:


   'Open the proper connection.
   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset

   Private Sub Command1_Click()
   'Getting the information about the columns in a particular table.
      Set rs = cn.OpenSchema(adSchemaColumns, Array("pubs", "dbo", _
             "authors", Null))
      MsgBox rs(0)
   End Sub

   Private Sub Command2_Click()
   'Getting the information about the primary key for a table.
      Set rs = cn.OpenSchema(adSchemaPrimaryKeys, Array("pubs", "dbo", _
             "authors"))
      MsgBox rs(3).Value
   End Sub

   Private Sub Command3_Click()
   'Getting the information about all the tables.
      Dim criteria(3) As Variant
      criteria(0) = "pubs"
      criteria(1) = Null
      criteria(2) = Null
      criteria(3) = "table"
      Set rs = cn.OpenSchema(adSchemaTables, criteria)
      While Not rs.EOF
         Debug.Print CStr(rs(2).Value)
         rs.MoveNext
      Wend

   End Sub

   Private Sub Form_Load()
      cn.Open "dsn=pubs;uid=sa;pwd=;"
   End Sub

More information on querytype and Criteria is available in the ADO documentation. The schema information specified in OLE DB is based upon the assumption that the provider supports the concept of a catalog and schema.

Additional query words: adoobj kbDSupport kbdse


Keywords          : kbDSupport 
Version           : WINDOWS:1.5,2.0,5.0
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: May 24, 1999