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()
    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
        rstSchema.MoveNext
    Loop
   
    ' clean up
    rstSchema.Close
    Cnxn.Close
    Set rstSchema = Nothing
    Set Cnxn = Nothing
    Exit Sub
    
ErrorHandler:
    ' 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
'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 Main()
    On Error GoTo ErrorHandler

    Dim Cnxn2 As ADODB.Connection
    Dim rstSchema As ADODB.Recordset
    Dim strCnxn As String
    
    Set Cnxn2 = New ADODB.Connection
    strCnxn = "Provider=sqloledb;" & _
        "Data Source=MySqlServer;Initial Catalog=Pubs;Integrated Security=SSPI; "
    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
      
    ' clean up
    rstSchema.Close
    Cnxn2.Close
    Set rstSchema = Nothing
    Set Cnxn2 = Nothing
    Exit Sub
    
ErrorHandler:
   ' clean up
    If Not rstSchema Is Nothing Then
        If rstSchema.State = adStateOpen Then rstSchema.Close
    End If
    Set rstSchema = Nothing
    
    If Not Cnxn2 Is Nothing Then
        If Cnxn2.State = adStateOpen Then Cnxn2.Close
    End If
    Set Cnxn2 = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndOpenSchema2VB

See Also

OpenSchema Method | Recordset Object