OpenSchema Method Example (VB)

This example uses the OpenSchema method to display the name and type of each table in the Pubs database.

'BeginOpenSchemaVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub OpenSchemaX()

   Dim Cnxn As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnxn As String
      
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
      
   Set rstSchema = Cnxn.OpenSchema(adSchemaTables)
   
   Do Until rstSchema.EOF
      Debug.Print "Table name: " & _
         rstSchema!TABLE_NAME & vbCr & _
         "Table type: " & rstSchema!TABLE_TYPE & vbCr
      rstSchema.MoveNext
   Loop
   
   ' clean up
   rstSchema.Close
   Cnxn.Close
   Set rstSchema = Nothing
   Set Cnxn = Nothing
   
End Sub
'EndOpenSchemaVB

This example specifies a TABLE_TYPE query constraint in the OpenSchema method Criteria argument. As a result, only schema information for the Views specified in the Pubs database are returned. The example then displays the name(s) and type(s) of each table(s).

'BeginOpenSchema2VB
Public Sub OpenSchemaX2()

   Dim Cnxn2 As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnxn As String
      
   Set Cnxn2 = New ADODB.Connection
      strCnxn = "Provider=sqloledb;" & _
      "Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn2.Open strCnxn
      
   Set rstSchema = Cnxn2.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "VIEW"))

      Do Until rstSchema.EOF
         Debug.Print "Table name: " & _
            rstSchema!TABLE_NAME & vbCr & _
            "Table type: " & rstSchema!TABLE_TYPE & vbCr
         rstSchema.MoveNext
      Loop
   rstSchema.Close
   
   Cnxn2.Close
   
End Sub
'EndOpenSchema2VB

See Also

OpenSchema Method | Recordset Object

© 1998-2001 Microsoft Corporation. All rights reserved.