Sub GetField2Description()
' This procedure:
' (1) deletes and recreates a table (tblFields)
' (2) uses a query of (table) MSysObjects to
' get the names of all tables and linked tables in the
' database
' (3) Populates tblFields with info about each field
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 fieldName As String
Dim fielddesc As String, tname As String
Dim n As Long, i As Long, recis As Variant
Dim found As Boolean, prpNew As Property
Dim fld As Field, strSQL As String, ordHold As Integer
n = 0
Set db = CurrentDb
' Trap for any errors.
On Error Resume Next
tname = "tblFields"
'Does table "tblFields" exist? If true, delete it;
found = False
test = db.TableDefs(tname).Name
If Err <> 3265 Then
found = True
docmd.DeleteObject acTable, "tblFields"
End If
'Create new tblTable
db.Execute "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldDesc TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FieldOrd Long);"
strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
strSQL = strSQL + "((MSysObjects.Type)=1)"
strSQL = strSQL + "ORDER BY MSysObjects.Name;"
Set rs = db.OpenRecordset(strSQL)
n = 0
If Not rs.BOF Then
' Get number of records in recordset
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
End If
Set rs2 = db.OpenRecordset("tblFields")
For i = 0 To n - 1
fielddesc = " "
Set td = db.TableDefs(i)
'Skip over any MSys objects
If Left(rs!Name, 4) <> "MSys" Then
namehold = rs!Name
found = False
On Error Resume Next
For Each fld In td.Fields
fieldName = fld.Name
fielddesc = fld.Properties("Description")
If Err = 3270 Or fielddesc = " " Then '3270 = object not found
fielddesc = "No description provided."
End If
Err = 0
typehold = FieldType(fld.Type)
SizeHold = fld.Size
rs2.AddNew
rs2!Object = namehold
rs2!fieldName = fieldName
rs2!fielddesc = fielddesc
rs2!FieldType = typehold
rs2!FieldSize = SizeHold
rs2!FieldAttributes = fld.Attributes
rs2.Update
Next fld
Resume Next
End If
rs.MoveNext
Next i
rs.Close
rs2.Close
db.Close
End Sub
Function FieldType(intType As Integer) As String
Select Case intType
Case dbBoolean
FieldType = "dbBoolean"
Case dbByte
FieldType = "dbByte"
Case dbInteger
FieldType = "dbInteger"
Case dbLong
FieldType = "dbLong"
Case dbCurrency
FieldType = "dbCurrency"
Case dbSingle
FieldType = "dbSingle"
Case dbDouble
FieldType = "dbDouble"
Case dbDate
FieldType = "dbDate"
Case dbText
FieldType = "dbText"
Case dbLongBinary
FieldType = "dbLongBinary"
Case dbMemo
FieldType = "dbMemo"
Case dbGUID
FieldType = "dbGUID"
End Select
End Function