Module: JetTableInfo in Category Access/Jet Databases : Table and Records from Total Visual SourceBook

Determine Microsoft Access/Jet database object information using DAO in VBA and VB6.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modJetTableInfo module.
IsTableField Procedure Determine if the field in the table in the specified database exists.
IsRecordsetField_DAO Procedure Determine if a field exists in a recordset.
IsTable Procedure Determine if the table in the specified database exists.
IsTableOrQuery Procedure Determine if name is a table or query within the specified database.
IsSytemTable Procedure Determine if the table in the specified database is a system table.
IsTableLinked Procedure Determine if the table in a named database is a linked table.
LinkedTableDatabase Procedure Determine the linked database for a table in a named database.
LinkedTableSource Procedure Retrieve a linked table's actual table name from its linked back-end database. Usually the table is the same name, but it may be different.
TableFieldDescription Procedure Get the description for a field from a table in the specified database. The description property does not exist if a description hasn't been assigned.
TableFieldType Procedure Get the field type ID for a field from a table in the specified database. The description property does not exist if a description hasn't been assigned.
TableFieldsToArray Procedure Populate an array with a list of fields in a table.
TableFieldsToArrays Procedure Populate three arrays with a list of fields, types, and descriptions from a table.
TableFieldsToString Procedure Populate a string with a delimited list of fields in a table from an existing database object.
TableType Procedure Retrieve the table's linked type.
FieldTypeString Procedure Convert a field type ID (number) to a string.
IsTableFieldRichText Procedure Determine if the Format property of a memo field is set to Rich Text. In Microsoft Office Access 2007 or later, you can store rich formatted text in a database. Do this by using a memo field and setting the field's TextFormat property to RichText. You can apply formatting to all or part of the contents of the field by editing the field through a bound control in a form, or when the field is displayed in a datasheet. You can also apply formatting to all of a field when viewing a report in Layout View.
TableIndexesToArray Procedure Fills an array with a list of indexes for a table.
TableIndexesToString Procedure Create a string with a list of indexes for a table.
IndexFieldsToArray Procedure Fills an array with a list of fields in an index.
IndexFieldsToString Procedure Populate a string with a delimited list of fields in an index.
GetColumnHistory Procedure In Access ACCDB format databases, the AppendOnly property for Memo fields allows you to store a history of the changes (set AppendOnly = Yes to collect column history). This code returns the ColumnHistory for the specified Memo field.
' Example of modJetTableInfo
'
' To try this example, do the following:
' 1. Create a new user form in your project.
' 2. Add the following command buttons to the form:
'       cmdTestTable
'       cmdTestField
'       cmdTestIndex
' 3. Paste this code into the form's module
' 4. Adjust the value of mcstrTable to the name of a table in the current database,
'    or import the Categories table from Sample.mdb (in the Total Visual SourceBook sample directory)
' 5. Run the form, and click the buttons to test the procedures in modJetTableInfo. Watch the Debug (or Immediate) window for the output.

Private Const mcstrTable As String = "Categories"

Private Sub cmdTestTable_Click()
  ' Comments: Examples of table procedures

  ' Test TableType
  Debug.Print "Table type: " & TableType(CurrentDb, mcstrTable)

  ' Test IsTable
  If IsTable(CurrentDb, mcstrTable) Then
    Debug.Print mcstrTable & " IS a table."
  Else
    Debug.Print mcstrTable & " IS NOT a table."
  End If

  ' Test IsTableOrQuery
  If IsTableOrQuery(CurrentDb, mcstrTable) Then
    Debug.Print mcstrTable & " IS a table or a query."
  Else
    Debug.Print mcstrTable & " IS NOT a table or a query."
  End If

  ' Test IsSystemTable
  If IsSytemTable(CurrentDb, mcstrTable) Then
    Debug.Print mcstrTable & " IS a system table."
  Else
    Debug.Print mcstrTable & " IS NOT a system table."
  End If

  ' Test IsTableLinked, LinkedTableDatabase, and LinkedTableSource
  If IsTableLinked(CurrentDb, mcstrTable) Then
    Debug.Print mcstrTable & " IS linked from " & LinkedTableSource(CurrentDb, mcstrTable)
    Debug.Print "               in: " & LinkedTableDatabase(CurrentDb, mcstrTable)
  Else
    Debug.Print mcstrTable & " IS NOT linked."
  End If

End Sub

Private Sub cmdTestField_Click()
  ' Comments: Examples of table field procedures

  Dim astrFields() As String
  Dim astrType() As String
  Dim astrDesc() As String
  Dim strFields As String
  Dim intFields As Integer
  Dim lngType As Integer
  Dim x As Integer

  ' See if a field exists in a table
  If IsTableField(CurrentDb, mcstrTable, "dummy") Then
    Debug.Print "Field was found"
  Else
    Debug.Print "Field was not found"
  End If

  ' TableFieldsToArray: Populates an array with a list of fields in a table
  intFields = TableFieldsToArray(CurrentDb, mcstrTable, False, astrFields)

  ' TableFieldType: Returns the field type ID for a field from a table in the specified database
  ' TableFieldDescription: Returns the description for a field from a table in the specified database
  ' IsTableFieldRichText: Determines if the Format property of a memo field is set to Rich Text. For 2007 only.
  For x = 0 To UBound(astrFields)
    lngType = TableFieldType(CurrentDb, mcstrTable, astrFields(x))
    Debug.Print x + 1, astrFields(x) & vbCrLf & _
      "              TypeID: " & lngType & vbCrLf & _
      "              Desc: " & TableFieldDescription(CurrentDb, mcstrTable, astrFields(x))
    ' If it's a Memo field (type 12), check if the format is Rich Text.
    If lngType = 12 Then
      Debug.Print "              RichText: " & IsTableFieldRichText(CurrentDb, mcstrTable, astrFields(x))
    End If
    Debug.Print vbCrLf
  Next x

  ' TableFieldsToArrays: Populates three arrays with a list of fields, types, and descriptions from a table
  intFields = TableFieldsToArrays(CurrentDb, mcstrTable, astrFields, astrType, astrDesc)
  If intFields > 0 Then
    For x = 0 To UBound(astrFields)
      Debug.Print x + 1, astrFields(x), astrType(x), astrDesc(x)
    Next x
  End If

  ' TableFieldsToString: Populates a string with a delimited list of fields in a table from an existing database object
  intFields = TableFieldsToString(CurrentDb, mcstrTable, ";", True, strFields)
  Debug.Print vbCrLf & strFields & vbCrLf

  ' FieldTypeString: Convert a field type ID (number) to a string
  Debug.Print CurrentDb.TableDefs(mcstrTable).Fields(0).name & " is a " & _
    FieldTypeString(CurrentDb.TableDefs(mcstrTable).Fields(0).Type, CurrentDb.TableDefs(mcstrTable).Fields(0).Attributes) & " field."

End Sub

Private Sub cmdTestIndex_Click()
  ' Comments: Examples of table index procedures

  Dim astrIndexes() As String
  Dim astrFields() As String
  Dim strNames As String
  Dim intFields As Integer
  Dim x As Integer
  Dim strIndexes As String
  Dim intCount As Integer
  Dim intCounter As Integer

  ' TableIndexesToArrayTest: Fills an array with a list of indexes for a table
  intFields = TableIndexesToArray(CurrentDb, mcstrTable, astrIndexes)
  For x = 0 To (intFields - 1)
    Debug.Print x + 1, astrIndexes(x)
  Next x

  ' TableIndexesToString: Creates a string with a list of indexes for a table
  intFields = TableIndexesToString(CurrentDb, mcstrTable, ";", strIndexes)

  Debug.Print strIndexes

  ' Example of IndexFieldsToArray
  intCount = IndexFieldsToArray(CurrentDb, mcstrTable, "PrimaryKey", astrFields)
  For intCounter = 0 To intCount - 1
    Debug.Print "Index field " & intCounter & ": " & astrFields(intCounter)
  Next intCounter

  ' Example of IndexFieldsToString
  intCount = IndexFieldsToString(CurrentDb, mcstrTable, "PrimaryKey", ";", strNames)
  Debug.Print "Fields in PrimaryKey of Order Details: " & strNames

End Sub

Private Sub Form_Load()
  ' Comments: Setup controls

  With Me.cmdTestTable
    .Caption = "Test Table Procs"
    .Top = 500
    .Left = 250
    .Width = 4000
  End With
  With Me.cmdTestField
    .Caption = "Test Table Field Procs"
    .Top = 1000
    .Left = 250
    .Width = 4000
  End With
  With Me.cmdTestIndex
    .Caption = "Test Table Index Procs"
    .Top = 1500
    .Left = 250
    .Width = 4000
  End With
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