Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot christmas Logo

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

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:


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:


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

98 Rating: 2.6/5 (31 votes cast)