OpenSchema Method Example (VB)

Microsoft ActiveX Data Objects (ADO)

OpenSchema Method Example (VB)

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 = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    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_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).

Public Sub OpenSchemaX2()

    Dim cnn2 As ADODB.Connection
    Dim rstSchema As ADODB.Recordset
    Dim strCnn As String
        
    Set cnn2 = New ADODB.Connection
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    cnn2.Open strCnn
        
    Set rstSchema = cnn2.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
    
    cnn2.Close
    
End Sub