openschema-method-example-vb.md

title	OpenSchema Method Example (VB) | Microsoft Docs
ms.prod	sql
ms.prod_service	connectivity
ms.technology	connectivity
ms.custom
ms.date	01/19/2017
ms.reviewer
ms.topic	conceptual
dev_langs	VB
helpviewer_keywords	OpenSchema method [ADO], Visual Basic example
ms.assetid	455a02f0-8143-4562-8648-8fb45ffd334c
author	MightyPen
ms.author	genemi

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 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  
        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).

Attribute VB_Name = "OpenSchema"  

See Also

OpenSchema Method
Recordset Object (ADO)