Thread: Code List Tables - missing the Linked Tables

Gee.. thanks for the help ( I say talking to myself).
Possibly the MS TechNet is wrong on the Table Type code. Found this by trial and error (see Case statement).
This code documents my Access Database with dozens of local tables and hundreds of SQL Server Linked tables. Shows each field and provides information on the field type.
It creates a Table in Access with this information.
The Excel side of this forum should provide information on how to use it in an Excel report.

Code:

Option Compare Database

Sub GetDBFieldDescriptions()
'***********************************************************   Comment ************************************************************
'Purpose:   1) Deletes and recreates a table (tblDocumentDatabase)
'           2) Queries table MSysObjects to return names of
'              all tables in the database and all fields with field properties
'           3) Populates tblDocumentDatabase
'Inputs:    From debug window:
'           Call GetDBFieldDescriptions
'Output:    See tblDocumentDatabase   - Object represents the local or linkedtable name, field name, fieldtype, fieldsize
' This will create/recreate a table tblDocumentDatabase and populate it with table/field info for all tables in an Access data base.
' Copy/past this code into a Objects New Modules, name RxDocumentDB
' invoke it by entering this into the Debug Window:      Call GetDBFieldDescriptions
'**********************************************************    End Comment *********************************************************

Dim db As Database, td As TableDef
Dim rs As Recordset, rs2 As Recordset
Dim Test As String, NameHold As String
Dim typehold As String, SizeHold As String
Dim fielddescription As String, tName As String
Dim n As Long, i As Long
Dim fld As Field, strSQL As String
Dim TableTypeHold As String
n = 0
Set db = CurrentDb
    On Error Resume Next   ' Trap for any errors.
tName = "tblDocumentDatabase"

'Does table "tblDocumentDatabase" already exist?  If true, delete it;
DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable, "tblDocumentDatabase"
DoCmd.SetWarnings True

'Create new tblTable  tblDocumentDatabase to hold documented results
db.Execute "CREATE TABLE tblDocumentDatabase(Object TEXT (55), FieldName TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, TableType TEXT (10), FldDescription TEXT (20));"

        strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
        strSQL = strSQL + "(((MSysObjects.Type) In (1, 4, 6)))"
        strSQL = strSQL + "ORDER BY MSysObjects.Name;"
' Note: 1 is actual tables & 4 is attached table & I found 6 in MS documentation, no clue what it is
Debug.Print "last run in Debug Windows at " & Now()
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
   ' Get number of records in recordset
   rs.MoveLast
   n = rs.RecordCount
   rs.MoveFirst
   Debug.Print "tblDocumentDatabase will have less than " & n & " table names (minus system tables)  DBEngine version " & DBEngine.Version
End If

Set rs2 = db.OpenRecordset("tblDocumentDatabase")

For i = 0 To n - 1
  fielddescription = " "
  Set td = db.TableDefs(i)
    'Skip over any MSys objects
    If Left(rs!Name, 4) <> "MSys" And Left(rs!Name, 1) <> "~" Then
      If UCase(Left(rs!Name, 1)) <> "U" And Left(rs!Name, 3) <> "sys" And Left(rs!Name, 19) <> "tblDocumentDatabase" Then
          ' skip 3rd party Add-in tables that starts tables with "u" skip system databases, skip the table this code creates
       NameHold = rs!Name
       
       Select Case CInt(rs!Type)  ' every field definition - table level indication (for sorting)
           Case 1
                TableTypeHold = "Local"
           Case 4
                TableTypeHold = "SQLLinked"
           Case Else
                TableTypeHold = "Unknown"
       End Select
       
       On Error Resume Next
       For Each fld In td.Fields
          fielddescription = fld.Name
          typehold = FieldType(fld.Type)
          SizeHold = fld.Size
          rs2.AddNew
          rs2!Object = NameHold
          rs2!FieldName = fielddescription
          rs2!FieldType = typehold
          rs2!FieldSize = SizeHold
          rs2!FieldAttributes = fld.Attributes
          rs2!tabletype = TableTypeHold
          rs2!FldDescription = fld.Properties("description")
          rs2.Update
       Next fld
  
       Resume Next
    End If
    End If
    rs.MoveNext
Next i
rs.Close
rs2.Close
db.Close
Debug.Print " Refresh tables view then open for the table named     tblDocumentDatabase "
End Sub

Function FieldType(intType As Integer) As String

Select Case intType
    Case dbBoolean
        FieldType = "dbBoolean"    '1
    Case dbByte
        FieldType = "dbByte"       '2
    Case dbInteger
        FieldType = "dbInteger"    '3
    Case dbLong
        FieldType = "dbLong"       '4
    Case dbCurrency
        FieldType = "dbCurrency"   '5
    Case dbSingle
        FieldType = "dbSingle"     '6
    Case dbDouble
        FieldType = "dbDouble"     '7
    Case dbDate
        FieldType = "dbDate"       '8
    Case dbBinary
        FieldType = "dbBinary"     '9
    Case dbText
        FieldType = "dbText"       '10
    Case dbLongBinary
        FieldType = "dbLongBinary" '11
    Case dbMemo
        FieldType = "dbMemo"       '12
    Case dbGUID
        FieldType = "dbGUID"       '15
End Select

End Function