HOWTO: Use the ADO OpenSchema Method in Visual Basic

ID: Q186246


The information in this article applies to:


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:

The following steps demonstrate the OpenSchema Method.

OpenSchema Method Example

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


  2. Add three Command buttons to the EXE project.


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


  4. 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