Module: JetObjectInfo in Category Access/Jet Databases : Database from Total Visual SourceBook

Detailed information on individual Jet/Access objects using the Data Access Objects (DAO) object model in VBA and VB6.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modJetObjectInfo module.
BuildDictionaryToArray Procedure Build a dictionary of Data Access Objects to an array. This includes tables, fields, queries, indexes, relations, forms, reports, macros and modules. The array is populated with both the object type and name in the form: [ObjectType][ObjectName]. For example, a table named 'Customers' appears in the array as [Table][Customers].
BuildDictionaryToTable Procedure Build a dictionary of Data Access Objects to a table. This includes tables, fields, queries, indexes, relations, forms, reports, macros and modules. The table populated is named 'tbl_DAO_Dictionary'. If the table doesn't exist, it is created. If it does exist, it is first emptied of records. The array is populated with both the object type and name in the form: [ObjectType][ObjectName]. For example, a table named 'Customers' appears in the array as [Table][Customers].
DumpDAOToText Procedure Dump the DAO structure of the database to a text file. This function creates a text file containing a list of objects in the current database, and optionally, their property names and values. If a path is omitted in the 'strFile' parameter, the file is created in the current directory.
DumpDAOToText_WriteOutput Procedure Write the string out to the file. This is a private helper function used by DumpDAOToText().
DumpDAOToText_WriteProperty Procedure Write the name and value of the supplied property. This is a private helper function used by DumpDAOToText().
GetQueryType Procedure Get the type of the named query: Select, Action, Crosstab, Delete, Update, Append, Make-table, Data-definition, Pass-through, or Union.
IsObjectInDB Procedure Determine if the named object is in the named Jet database.
NumberObjects Procedure Get the number of objects in a database, including tables, queries, forms, reports, macros, modules and relationships. System objects (such as the MSYS... tables) are not counted.
ObjectsToArray Procedure Populate the passed array with a list of object names of the specified type.
ObjectsToString Procedure Populate the supplied string with a delimited list of object names of the specified type.
QueryFieldsToArray Procedure Populate the passed array with a list of fields in the named query.
QueryFieldsToString Procedure Populate the passed string with a list of fields returned by the named query.
QueryParametersToArray Procedure Populate the passed array with a list of parameters returned by the named query.
QueryParametersToString Procedure Populate the passed string with a list of parameters returned by the named query.
RelationFieldsToArray Procedure Populate the passed array with a list of fields in the named relation.
RelationFieldsToString Procedure Populate the passed string with a list of fields in the named relation.
' Example of modJetObjectInfo
'
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Private Sub Example_modJetObjectInfo()
  ' Comments: Example of using the modJetObjectInfo module to retrieve Microsoft Access object property information in VBA and VB6.
  '           See the results in the Immediate Window.

  Const cstrDatabase As String = "C:\Total Visual SourceBook 2013\Samples\Northwind.mdb"
  Const cstrFile As String = "C:\Total Visual SourceBook 2013\Samples\daodump.txt"
  Dim dbsNwind As DAO.Database
  Dim strNames As String
  Dim astrNames() As String
  Dim intCounter As Integer
  Dim intCount As Integer

  Set dbsNwind = DAO.OpenDatabase(cstrDatabase)

  ' Example of BuildDictionaryToArray
  BuildDictionaryToArray dbsNwind, astrNames
  For intCounter = LBound(astrNames) To UBound(astrNames)
    Debug.Print astrNames(intCounter)
  Next intCounter

  ' Example of BuildDictionaryToTable
  BuildDictionaryToTable dbsNwind, "FMS_Dict_TEMP"

  ' Example of GetQueryType
  Debug.Print "The query type for Orders Qry is: " & GetQueryType(dbsNwind, "Orders Qry")

  ' Example of IsObjectInDB
  If IsObjectInDB(dbsNwind, "table", "Products") Then
    Debug.Print "Products table is in the database"
  Else
    Debug.Print "Products table is not in the database"
  End If

  ' Example of NumberObjects
  intCount = NumberObjects(dbsNwind)
  Debug.Print "Number of objects: " & intCount

  ' Example of ObjectsToArray
  intCount = ObjectsToArray(dbsNwind, "table", astrNames)
  For intCounter = 0 To intCount - 1
    Debug.Print astrNames(intCounter)
  Next intCounter

  ' Example of ObjectsToString
  intCount = ObjectsToString(dbsNwind, "table", strNames)
  Debug.Print "Tables: " & strNames

  ' Example of QueryFieldsToArray
  intCount = QueryFieldsToArray(dbsNwind, "Orders Qry", astrNames)
  For intCounter = 0 To intCount - 1
    Debug.Print "Query Field " & intCounter & ": " & astrNames(intCounter)
  Next intCounter

  ' Example of QueryFieldsToString
  intCount = QueryFieldsToString(dbsNwind, "Orders Qry", strNames)
  Debug.Print "Query Fields: " & strNames

  ' Example of QueryParametersToArray
  intCount = QueryParametersToArray(dbsNwind, "Employee Sales By Country", astrNames)
  For intCounter = 0 To intCount - 1
    Debug.Print "Query parameter " & intCounter & ": " & astrNames(intCounter)
  Next intCounter

  ' Example of QueryParametersToString
  intCount = QueryParametersToString(dbsNwind, "Employee Sales By Country", strNames)
  Debug.Print "Query Parameters: " & strNames

  ' Example of RelationFieldsToArray
  intCount = RelationFieldsToArray(dbsNwind, "CategoriesProducts", astrNames)
  For intCounter = 0 To intCount - 1
    Debug.Print "Relation field " & intCounter & ": " & astrNames(intCounter)
  Next intCounter

  ' Example of RelationFieldsToString
  intCount = RelationFieldsToString(dbsNwind, "CategoriesProducts", strNames)
  Debug.Print "Relation Fields: " & strNames

  ' Example of DumpDAOToText
  If DumpDAOToText(dbsNwind, cstrFile, True) Then
    Debug.Print "Output file created"
  Else
    Debug.Print "Output file was not created"
  End If

End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS