You are currently offline, waiting for your internet to reconnect

How To Use the ADO OpenSchema Method in Visual Basic

This article was previously published under Q186246
For a Microsoft Visual Basic .NET version of this article, see 309488.
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. Examples are adSchemaTables, adSchemaPrimaryKeys, adSchemaColumns.
  • 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. The criteria Array member indicates the string values to restrict the query results. The number of the array members varies depending on the querytype.
  • 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.
Below are the sample parameters for OpenSchema. Note that the criteria changes with the querytype. The most important thing to remember is that the order of providing the values has to be the same. A list of corresponding criteria for other querytypes is in the online documentation located at the following URL:
   QueryType        Criteria   =============================   adSchemaTables   TABLE_CATALOG                    TABLE_SCHEMA                    TABLE_NAME                    TABLE_TYPE				
Use adSchemaTables to list the tables in a database.

Microsoft Access 97 and Access 2000

To list all of the tables and queries in the Microsoft Access NWind database, simply use the following code:
Set rs = cn.OpenSchema(adSchemaTables)   While Not rs.EOF      Debug.Print rs!TABLE_NAME      rs.MoveNext   Wend				
To list only the tables in the Access Nwind database, use:
Set rs = cn.OpenSchema(adSchemaTables, _         Array(Empty, Empty, Empty, "Table")				
Use the same syntax, using the OLE DB Provider for ODBC with the Jet ODBC driver and using the Jet OLE DB Providers.

Microsoft SQL Server 6.5 and 7.0

To list all of the tables and views in the Microsoft SQL Server Pubs Database, use:
Set rs = cn.OpenSchema(adSchemaTables)				
To list just the tables in the Microsoft SQL Server Pubs database, use:
Set rs = cn.OpenSchema(adSchemaTables, _         Array("Pubs", Empty, Empty, "Table")				
Use the same syntax using the OLE DB Provider for ODBC with the SQL Server ODBC driver and using the OLE DB Provider for SQL Server.
   QueryType         Criteria   ===============================   adSchemaColumns   TABLE_CATALOG                     TABLE_SCHEMA                     TABLE_NAME                     COLUMN_NAME				
Use adSchemaColumns to list the fields in a table.

Microsoft Access 97 and Access 2000

To list the fields in the Employees table in the Access Nwind database With adSchemaColumns, simply use:
Set rs = cn.OpenSchema(adSchemaColumns,Array(Empty, Empty, "Employees")While Not rs.EOF   Debug.Print rs!COLUMN_NAME   rs.MoveNextWend				
This works using the OLE DB Provider for ODBC with the Jet ODBC Driver and using with the Jet OLE DB Providers.

Microsoft SQL Server 6.5 and 7.0

To list the fields in the Authors table in the SQL Server Pubs database with adSchemaColumns, simply use:
Set rs = cn.OpenSchema(adSchemaColumns, Array("pubs", "dbo", "Authors")				
Note that TABLE_CATALOG is the database and TABLE_SCHEMA is the table owner. This works using the OLE DB Provider for ODBC with the SQL Server ODBC driver and using the OLE DB Provider for SQL Server.
   QueryType          Criteria   ================================   adSchemaIndexes    TABLE_CATALOG                      TABLE_SCHEMA                      INDEX_NAME                      TYPE                      TABLE_NAME				
You provide the index name in case of adSchemaIndexes querytype.

Microsoft Access 97 and Access 2000

To list the Indexes in the Employees table in the Access Nwind database With adSchemaIndexes, simply use:
Set rs = cn.OpenSchema(adSchemaIndexes, _         Array(Empty, Empty, Empty, Empty, "Employees")While Not rs.EOF   Debug.Print rs!INDEX_NAME   rs.MoveNextWend				
This works using the OLE DB Provider for ODBC with the Jet ODBC Driver and using with the Jet OLE DB Providers.

Microsoft SQL Server 6.5 and 7.0

To list the Indexes in the Authors table in the SQL Server Pubs database with adSchemaIndexes, simply use:
Set rs = cn.OpenSchema(adSchemaIndexes, _         Array("Pubs", "dbo", Empty, Empty, "Authors")				
This works using the OLE DB Provider for ODBC with the SQL Server ODBC driver and using the OLE DB Provider for SQL Server. 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:

Note You will need to change UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.
   '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"))      While Not rs.EOF          Debug.Print rs!COLUMN_NAME          rs.MoveNext      Wend   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!COLUMN_NAME   End Sub   Private Sub Command3_Click()   'Getting the information about all the tables.      Dim criteria(3) As Variant      criteria(0) = "pubs"      criteria(1) = Empty      criteria(2) = Empty      criteria(3) = "table"      Set rs = cn.OpenSchema(adSchemaTables, criteria)      While Not rs.EOF         Debug.Print rs!TABLE_NAME         rs.MoveNext      Wend   End Sub   Private Sub Form_Load()      cn.Open "dsn=pubs;uid=<username>;pwd=<strong password>;"      'To test with the Native Provider for SQL Server, comment the      ' line above then uncomment the following line. Modify to use      ' your server.      'cn.Open "Provider=SQLOLEDB;Data Source=<servername>;" & _      '        "User ID=sa;password=;"   End Sub				
Run. Click each Command button to test. End.Modify the Form Load event procedure to use the Native Provider for SQL Server. Again test. 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.
REFERENCES
For more information on OpenSchema, please see the following articles in the Microsoft Knowledge Base:
182831 How To Using the ADO OpenSchema Method from Visual C++
185979 How To Use ADO to Retrieve Table Index Information
adoobj
Properties

Article ID: 186246 - Last Review: 12/04/2007 03:44:52 - Revision: 5.6

Microsoft ActiveX Data Objects 1.5, Microsoft ActiveX Data Objects 2.0, Microsoft ActiveX Data Objects 2.1 Service Pack 2, Microsoft ActiveX Data Objects 2.5, Microsoft ActiveX Data Objects 2.6, Microsoft ActiveX Data Objects 2.7, Microsoft Visual Basic 5.0 Professional Edition, Microsoft Visual Basic 6.0 Professional Edition, Microsoft Visual Basic 5.0 Enterprise Edition, Microsoft Visual Basic Enterprise Edition for Windows 6.0

  • kbdatabase kbhowto kbjet kbprovider KB186246
Feedback
var varAutoFirePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" ng-click="setLanguage(language);" class="ng-binding" id="language-es-pa">Panamá - Español
Uruguay - Español
대한민국 - 한국어
España - Español
Paraguay - Español
Venezuela - Español
>://c1.microsoft.com/c.gif?DI=4050&did=1&t="> var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" ')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> y>