Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

ADO Schemas

From Wiki

Jump to: navigation, search

You can have a lot of fun with schemas. Do not forget to substitute the values for the built-in constants when using ADO schemas in VBScript, for example, adSchemaTables=20 and adSchemaColumns=4.

Example: List All Tables and Views

  Public Function AllTablesAdo() As Boolean
  Dim rs As ADODB.Recordset
  
      Set rs = CurrentProject.Connection.OpenSchema( _
          adSchemaTables, Array(Empty, Empty, Empty))
      Debug.Print rs.GetString
      Set rs = Nothing
  End Function

The fields returned are:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
TABLE_GUID
Description
TABLE_PROPID
DATE_CREATED
DATE_MODIFIED

Table_Type can be:

Type	Description
ACCESS TABLE	The table is an Access system table.
LINK	The table is a linked table from a non-ODBC data source.
PASS-THROUGH	The table is a linked table from an ODBC data source.
SYSTEM TABLE	The table is a Microsoft Jet system table.
TABLE	The table is a standard table.
VIEW	The table is a query that has no parameters and returns records.

Example: Find if a table exists

   Public Function FindTableAdo(ByVal vstrTable As String) As Boolean
   'Table exists using schema
   Dim rs As ADODB.Recordset
   
       Set rs = CurrentProject.Connection.OpenSchema( _
           adSchemaTables, Array(Empty, Empty, vstrTable))
      
       If Not rs.EOF Then
          If rs!Table_Type <> "View" Then
             FindTableAdo = True
          End If
       End If
      
       rs.Close
       Set rs = Nothing
   End Function

Example: Find if a query exists

Thanks to Zameer Abdulla, we can say that this example shows only SELECT Queries. You need to use adSchemaProcedures to show action queries. ([1])

   Public Function FindQueryAdo(ByVal vstrQuery As String) As Boolean
   'Query exists using schema
   Dim rs As ADODB.Recordset
       Set rs = CurrentProject.Connection.OpenSchema( _
           adSchemaViews, Array(Empty, Empty, vstrQuery))
       FindQueryAdo = Not rs.EOF
       rs.Close
       Set rs = Nothing
   End Function

Example: List some field properties, including the description

   Function ListFieldDescriptions()
   'List field descriptions
   Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
   Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset
  
       Set cn = CurrentProject.Connection
    
       Set rs = cn.OpenSchema(adSchemaTables, _
           Array(Empty, Empty, Empty, "tablenamehere"))
      
       While Not rs.EOF
           Debug.Print rs!table_name; "   desc=  "; rs!Description
           Set rs2 = cn.OpenSchema(adSchemaColumns, _
               Array(Empty, Empty, "" & rs!table_name & ""))
           While Not rs2.EOF
               Debug.Print "     " & rs2!Column_Name
               Debug.Print "     " & rs2!Data_Type
               Debug.Print "     " & rs2!Description
               Debug.Print "     " & rs2!Is_Nullable
               rs2.MoveNext
           Wend
       rs.MoveNext
       Wend
       rs.Close
       Set cn = Nothing
    
   End Function

Example: Return a list of tables containing a particular field (column)

   Function ListTablesContainingField(SelectFieldName) As String
   'Tables returned will include linked tables
   'I have added a little error coding. I don't normally do that
   'for examples, so don't read anything into it :) 
   Dim cn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strTempList As String
   
       On Error GoTo Error_Trap
   
       Set cn = CurrentProject.Connection
   
       'Get names of all tables that have a column called <SelectFieldName>
       Set rs = cn.OpenSchema(adSchemaColumns, _
       Array(Empty, Empty, Empty, SelectFieldName))
   
       'List the tables that have been selected
       While Not rs.EOF
           'Exclude MS system tables
           If Left(rs!Table_Name, 4) <> "MSys" Then
               strTempList = strTempList & "," & rs!Table_Name
           End If
           rs.MoveNext
       Wend
   
       ListTablesContainingField = Mid(strTempList, 2)
   
   Exit_Here:
   
       rs.Close
       Set cn = Nothing
       Exit Function
   
   Error_Trap:
   
       MsgBox Err.Description
       Resume Exit_Here
   End Function

The fields returned by adSchemaColumns are:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_GUID
COLUMN_PROPID
ORDINAL_POSITION
COLUMN_HASDEFAULT
COLUMN_DEFAULT
COLUMN_FLAGS
IS_NULLABLE
DATA_TYPE
TYPE_GUID
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
DESCRIPTION

Example: List users

This sample sub, which uses a provider-specific schema, will list users. You need to pass a valid connection, for example:

  ADOUserList Currentproject.Connection

  Public Sub ADOUserList(oConn As ADODB.Connection)
  Dim rs As ADODB.Recordset
      Set rs = oConn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
      Debug.Print rs.GetString
      rs.Close
  End Sub

The fields returned by rs.GetString are:

Column	Description
COMPUTER_NAME	The name of the user's computer.
LOGIN_NAME	Either Admin or name, depending on whether the database has been secured with user-level security.
CONNECTED	True, if there is a corresponding user lock in the lock file (.ldb).
SUSPECTED_STATE	True, if the user has left the database in a suspect state; for example, if the user didn't exit the database normally due to loss of power or a system failure. Otherwise the value in this column will be Null.

Schemas

adSchemaAsserts
adSchemaCatalogs
adSchemaCharacterSets
adSchemaCheckConstraints
adSchemaCollations
adSchemaColumnDomainUsage
adSchemaColumnPrivileges
adSchemaColumns
adSchemaConstraintColumnUsage
adSchemaConstraintTableUsage
adSchemaForeignKeys
adSchemaIndexes
adSchemaKeyColumnUsage
adSchemaPrimaryKeys
adSchemaProcedureColumns
adSchemaProcedureParameters
adSchemaProcedures
adSchemaProviderSpecific - See Remarks
adSchemaProviderTypes
adSchemaReferentialConstraints
adSchemaSQLLanguages - <none>
adSchemaSchemata
adSchemaStatistics
adSchemaTableConstraints
adSchemaTablePrivileges
adSchemaTables
adSchemaTranslations
adSchemaUsagePrivileges
adSchemaViewColumnUsage
adSchemaViewTableUsage
adSchemaViews

Further Information

Use ADO to Return a List of Users Connected to a Database: http://msdn.microsoft.com/en-us/library/aa155436.aspx

Listing the Tables in an Access Database: http://msdn.microsoft.com/en-us/library/aa165325(office.10).aspx

Understanding the Provider-Specific Schema Collections: http://msdn.microsoft.com/en-us/library/ms254969(VS.80).aspx (includes Jet OLEDB and ODBC Drivers, along with Oracle and SQL)

Obtaining Schema Information from a Database: http://msdn.microsoft.com/en-us/library/kcax58fh(VS.80).aspx

Microsoft OLE DB Provider for Microsoft Jet: http://msdn.microsoft.com/en-us/library/ms681754(VS.85).aspx