Determining The Layout Of Your Data Source In ActiveX Data Objects

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.

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