ADO: ActiveX Data Objects

Determining the Layout of Your Data Source

Your data source has many characteristics that can be exposed to your applications via ADO. For instance, some data sources have table names, field names, and indexes. You can use the OpenSchema method of the Connection object to enumerate the characteristics that make up the structure of your data source. The OpenSchema method has the following syntax:

Set recordset_name = connection_name.OpenSchema(QueryType, Criteria, SchemaID)

Table 4-7 describes each of the components found in the previous syntax declaration.

Table 4-7. The Components of the OpenSchema Method

Component	Description
recordset_name	A valid Recordset object.
connection_name	A currently open Connection object.
QueryType	Indicates what type of schema query to perform on the associated connection object. This value must be a valid constant that belongs to the SchemaEnum enumeration. Not all QueryType values are supported by every data source. See the OpenSchema method in Appendix C, for more information.
Criteria	Optional. Indicates a specific constraint used to perform the query as defined by the QueryType argument. Criteria values are specific to each QueryType value, and, because not all QueryTypes are supported by every data source, neither are all Criteria values.
SchemaID	Optional. A GUID for a provider-specific schema that is used only with the QueryType constant, adSchemaProviderSpecific.

In most cases, your data provider will not support all of the Criteria constraints. As a matter of fact, the data provider must supply you with only the adSchemaTables, adSchemaColumns, and adSchemaProviderTypes constants, according to the OLE DB specification. For a list of all the constraints available, refer to the SchemaEnum enumeration in Appendix E.

Example 4-19 utilizes the OpenSchema method to create a viewer for all possible QueryType and Criteria combinations for a given data source.

To create this example, first open up a new Application EXE project within Visual Basic, and add the latest version of Microsoft ActiveX Data Objects through the Project → References tool item. Next, add the controls listed in Table 4-8, and name them accordingly.

Table 4-8. The Components of the OpenSchema Method

Control	Name
ListBox Control	lstQueryType
ListBox Control	lstCriteria
ListBox Control	lstValue
Command Button	cmdClose

Now, replacing the code that is already in the Form1 form dialog box, enter the code for the modular-level object variables, as shown in Example 4-19. This example will use the OLE DB provider for ODBC connections and the SQL Server DSN, SQLNorthwindDSN. If you do not have SQL Server, you can replace this DSN with the BiblioDSN DSN (see Section 4.2.2.1 earlier in this chapter).

Example 4-19. The Schema Viewer Example

Option Explicit

Private con As ADODB.Connection
Private rst As ADODB.Recordset

Private Sub Form_Load(  )

    Set con = New ADODB.Connection
    Set rst = New ADODB.Recordset

    ' connect to our data source
    con.Open "SQLNorthwindDSN"
        
    ' populate the query types list box with valid query type values for
    ' this particular data source
    Call PopulateQueryTypes
        
End Sub

Private Sub PopulateQueryTypes(  )
    
    lstQueryType.Clear
    
    ' call the CheckQueryType function for each possible query type value
    CheckQueryType "adSchemaCatalogs: " & adSchemaCatalogs
    
    CheckQueryType "adSchemaCharacterSets: " & adSchemaCharacterSets
    CheckQueryType "adSchemaCheckConstraints: " & adSchemaCheckConstraints
    CheckQueryType "adSchemaCollations: " & adSchemaCollations
    CheckQueryType "adSchemaColumnPrivileges: " & adSchemaColumnPrivileges
    CheckQueryType "adSchemaColumns: " & adSchemaColumns
    CheckQueryType "adSchemaColumnsDomainUsage: " _
                   & adSchemaColumnsDomainUsage
    CheckQueryType "adSchemaConstraintColumnUsage: " _
                   & adSchemaConstraintColumnUsage
    CheckQueryType "adSchemaConstraintTableUsage: " _
                   & adSchemaConstraintTableUsage
    CheckQueryType "adSchemaCubes: " & adSchemaCubes
    CheckQueryType "adSchemaDBInfoKeywords: " & adSchemaDBInfoKeywords
    CheckQueryType "adSchemaDBInfoLiterals: " & adSchemaDBInfoLiterals
    CheckQueryType "adSchemaDimensions: " & adSchemaDimensions
    CheckQueryType "adSchemaForeignKeys: " & adSchemaForeignKeys
    CheckQueryType "adSchemaHierarchies: " & adSchemaHierarchies
    CheckQueryType "adSchemaIndexes: " & adSchemaIndexes
    CheckQueryType "adSchemaKeyColumnUsage: " & adSchemaKeyColumnUsage
    CheckQueryType "adschemaLevels: " & adSchemaLevels
    CheckQueryType "adSchemaMeasures: " & adSchemaMeasures
    CheckQueryType "adSchemaMembers: " & adSchemaMembers
    CheckQueryType "adSchemaPrimaryKeys: " & adSchemaPrimaryKeys
    CheckQueryType "adSchemaProcedureColumns: " & adSchemaProcedureColumns
    CheckQueryType "adSchemaProcedureParameters: " _
                   & adSchemaProcedureParameters
    CheckQueryType "adSchemaProcedures: " & adSchemaProcedures
    CheckQueryType "adSchemaProperties: " & adSchemaProperties
    CheckQueryType "adSchemaProviderTypes: " & adSchemaProviderTypes
    CheckQueryType "adSchemaReferentialContraints: " _
                   & adSchemaReferentialContraints
    CheckQueryType "adSchemaSchemata: " & adSchemaSchemata
    
    CheckQueryType "adSchemaSQLLanguages: " & adSchemaSQLLanguages
    CheckQueryType "adSchemaStatistics: " & adSchemaStatistics
    CheckQueryType "adSchemaTableConstraints: " & adSchemaTableConstraints
    CheckQueryType "adSchemaTablePrivileges: " & adSchemaTablePrivileges
    CheckQueryType "adSchemaTables: " & adSchemaTables
    CheckQueryType "adSchemaTranslations: " & adSchemaTranslations
    CheckQueryType "adSchemaUsagePrivileges: " & adSchemaUsagePrivileges
    CheckQueryType "adSchemaViewColumnUsage: " & adSchemaViewColumnUsage
    CheckQueryType "adSchemaViews: " & adSchemaViews
    CheckQueryType "adSchemaViewTableUsage: " & adSchemaViewTableUsage
    
End Sub

The CheckQueryType method determines if a schema is available by attempting to open it with the OpenSchema method:

Private Sub CheckQueryType(sQueryType As String)
On Error GoTo ERR_CheckQueryType:
    
    ' if we can open the schema without getting an error, the data source
    ' will support it, otherwise, do not add it to the list box
    Set rst = con.OpenSchema(GetQueryTypeValue(sQueryType))
    
ERR_CheckQueryType:
    Select Case Err.Number
        Case 0:
            lstQueryType.AddItem (sQueryType)
        Case adErrFeatureNotAvailable:
            ' not supported
    End Select
    
End Sub

When a user clicks on a query-type list box, the corresponding schema is opened and used to populate the criteria list box:

Private Sub lstQueryType_Click(  )
    
    Dim lTemp As Long
    Dim fld As ADODB.Field
    
    lstCriteria.Clear
    lstValue.Clear
    
    ' get the value of the query type from parsing the string 
    ' that is selected
    lTemp = GetQueryTypeValue(lstQueryType.List(lstQueryType.ListIndex))
    
    ' open the schema for the query type chosen
    Set rst = con.OpenSchema(lTemp)
    
    ' add criterias that are available for the query type to the list box
    
    For Each fld In rst.Fields
        lstCriteria.AddItem fld.Name
    Next fld
    
End Sub

In turn, as the criteria list box is selected, the individual values for the criteria are added to the values list box:

Private Sub lstCriteria_Click(  )
    
    lstValue.Clear
    
    ' populate the values list box with the values for the selected
    ' query type and criteria
    If (Not (rst.EOF And rst.BOF)) Then rst.MoveFirst
    Do Until (rst.EOF)
        lstValue.AddItem _
                ConvertToString(rst.Fields(lstCriteria.ListIndex).Value)
        rst.MoveNext
    Loop

End Sub

Now enter the remaining utility and termination methods, and you are done:

Private Function ConvertToString(vInput As Variant) As String
    
    ' return the 'Null' string if the value is null, otherwise return the
    ' actual string
    If IsNull(vInput) Then
        ConvertToString = "Null"
    Else
        ConvertToString = vInput
    End If

End Function

Private Function GetQueryTypeValue(sQueryType As String) As Long

    Dim sTemp As String
    
    ' take the number (value of the query type) off of the string
    sTemp = Right$(sQueryType, Len(sQueryType) - InStr(1, sQueryType, ":"))
    
    GetQueryTypeValue = Val(sTemp)
    
End Function

Private Sub cmdClose_Click(  )
    
    ' clean up recordset object
    rst.Close
    Set rst = Nothing
    
    ' clean up connection object
    con.Close
    Set con = Nothing
    
    ' end the application
    Unload Me
    
End Sub

When this application is compiled and run, it should produce a result similar to Figure 4-8, assuming your data source is similar to mine.

Figure 4-8. The Schema Viewer application in action

The OpenSchema method is very useful for finding such information about the data source as table names, stored procedure and query names, index information, table names, as well as a number of other valuable pieces of information. I suggest that you use the Schema Viewer application from Example 4-19 on your data source to see what is available and what you would find useful within your own application.

Tip

The OpenSchema method is not available to a client-side Connection object when using RDS.