Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
OpenSchema Method Example (VB)
Collapse the table of content
Expand the table of content

OpenSchema Method Example (VB)


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


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

Public Sub Main()
    On Error GoTo ErrorHandler

    Dim Cnxn As ADODB.Connection
    Dim rstSchema As ADODB.Recordset
    Dim strCnxn As String

    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    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

    ' clean up
    Set rstSchema = Nothing
    Set Cnxn = Nothing
    Exit Sub

    ' clean up
    If Not rstSchema Is Nothing Then
        If rstSchema.State = adStateOpen Then rstSchema.Close
    End If
    Set rstSchema = Nothing

    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
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).

Attribute VB_Name = "OpenSchema"
© 2015 Microsoft