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

' 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

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

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)