Get Table Information

[ajetrumpet]

This was kinda fun to write, so I thought I'd post it here. The function creates a table to display information about user-created tables. It also uses a separate function to convert data type values to readable strings. It is not all-inclusive, but it converts most of the types you will probably work with in your tables. The main function gives you the names, data types, and sizes of every field found in a table:

Function GetTableData()

On Error GoTo ErrorHandler

  Dim fld As Field
    Dim str As String
      Dim i As Integer
        Dim db As Database
          Set db = CurrentDb()
            Dim rsNEW As Recordset
              Dim rsLOOP As Recordset
                Dim tbldef As TableDef

Set tbldef = db.CreateTableDef("DBdata")

  With tbldef
    .Fields.Append .CreateField("TableName", dbText)
    .Fields.Append .CreateField("FieldName", dbText)
    .Fields.Append .CreateField("FieldType", dbText)
    .Fields.Append .CreateField("FieldSize", dbText)
  End With

    db.TableDefs.Append tbldef

  Set rsNEW = db.OpenRecordset("DBdata")

    For Each tbldef In db.TableDefs

      If Not tbldef.Name Like "msys*" Then
        If Not tbldef.Name Like "~tmp*" Then
          If Not tbldef.Name = "DBdata" Then

            Set rsLOOP = db.OpenRecordset(tbldef.Name)

              For i = 0 To rsLOOP.Fields.Count - 1
                Set fld = rsLOOP.Fields(i)

                With rsNEW
                  .AddNew
                  !tablename = tbldef.Name
                  !FieldName = fld.Name
                  !FieldType = FieldType(fld.Type)
                  !FieldSize = fld.Size
                  .Update
                End With

              Next i

            rsLOOP.Close

          End If
        End If
      End If

    Next tbldef

  Set fld = Nothing
  Set rsNEW = Nothing
  Set rsLOOP = Nothing

ErrorHandler:
  Exit Function

End Function

Conversion Function:

Public Function FieldType(IntegerType As Integer) As String

  Select Case IntegerType
  
    Case dbText
      FieldType = "Text"
    Case dbMemo
      FieldType = "Memo"
    Case dbByte
      FieldType = "Byte"
    Case dbSingle
      FieldType = "Single"
    Case dbDouble
      FieldType = "Double"
    Case dbDecimal
      FieldType = "Decimal"
    Case dbInteger
      FieldType = "Integer"
    Case dbBoolean
      FieldType = "Boolean"
    Case dbCurrency
      FieldType = "Currency"
    Case dbAttachment
      FieldType = "Attachment"
    Case dbDate
      FieldType = "Date / Time"
    Case dbLong
      FieldType = "Long Integer"
    Case Else
      FieldType = "Other"

  End Select

End Function

Either one of the attached files demonstrate what the outcome looks like.