OpenSchema Method Example

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

Public Sub OpenSchemaX()

   Dim cnn1 As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnn As String
      
   Set cnn1 = New ADODB.Connection
   strCnn = "driver={SQL Server};server=srv;" & _
      "uid=sa;pwd=;database=pubs"
   cnn1.Open strCnn
      
   Set rstSchema = cnn1.OpenSchema(adSchemaTables)
   
   Do Until rstSchema.EOF
      Debug.Print "Table name: " & _
         rstSchema!TABLE_NAME & vbCr & _
         "Table type: " & rstSchema!TABLE_TYPE & vbCr
      rstSchema.MoveNext
   Loop
   rstSchema.Close
   
   cnn1.Close
   
End Sub

This example specifies a TABLE_NAME query constraint in the OpenSchema method Criteria argument. As a result, only schema information for the Authors table of the Pubs database is returned. The example then displays the name and type of that table.

Public Sub OpenSchemaX2()

   Dim cnn2 As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnn As String
      
   Set cnn2 = New ADODB.Connection
   strCnn = "driver={SQL Server};server=srv;" & _
      "uid=sa;pwd=;database=pubs"
   cnn2.Open strCnn
      
   Set rstSchema = cnn2.OpenSchema(adSchemaTables, Array(,,"authors",))
   Debug.Print _
      "Table name: " & rstSchema!TABLE_NAME & vbCr & _
      "Table type: " & rstSchema!TABLE_TYPE & vbCr
   rstSchema.Close
   
   cnn2.Close
   
End Sub