Microsoft Access, Office/VBA, VB6 Modules royalty-free source code library

Microsoft Access Modules royalty-free source code library

Microsoft Access VBA and VB6 Module Library

Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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 List

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 Code for Using Module: JetObjectInfo

' 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

Overview of 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!