Total Visual SourceBook

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 2007 and VB6

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

Separate version for Access/Office 2003, 2002, and 2000.

Separate version for:
Access 97/95


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

SourceBook Info:

Why SourceBook?

 

"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

 

 

 

Microsoft Access, Office/VBA, VB6 Modules royalty-free source code libraryMicrosoft Access Modules royalty-free source code libraryMicrosoft Access VBA and VB6 Module Library Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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 List

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

' 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

Microsoft Access Module LibraryOverview 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.

Additional Resources