Exporting table field names to Word/Excel (Just Field Names not da

[Guest]

Is there an easy way to do this? I have several tables that I merge into one
using a union query but they don't all have the same fields. I need to have
an easy way to identify the fields that don't appear in all the tables. See
example:

PERSON_ID
FIRST_NAME
LAST_NAME
STREET_ADDRESS
CITY
STATE
ZIP

This would be all I want to export not all of the actual contents within the
table. This seems like it would be a simple thing to do, but I just don't
know how.

 

[Arvin Meyer]

This will put all the field names in a table, which you can then export to
Word or Excel. This is an old piece of code which you should disambiguate
for Access 2000 and greater.

Function GetFields()
' Arvin Meyer 12/7/1995
' Modified 3/17/2000 Tim Mills-Groninger
' Finds all fields in a table

On Error Resume Next

Dim db As Database
Dim rst As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim intI As Integer
Dim intJ As Integer
Dim strDesc As String

Set db = CurrentDb()
' Clean out the "working" table tblFields
db.Execute "Delete * From tblFields"
' Open the table for data entry
Set rst = db.OpenRecordset("tblFields", DB_OPEN_TABLE, DB_APPENDONLY)
' Outer loop for tables
For intI = 0 To db.TableDefs.Count - 1
Set tdf = db.TableDefs(intI)
' Skip system tables
If Left(tdf.Name, 4) <> "MSys" Then
' Now loop through fields
For intJ = 0 To tdf.Fields.Count - 1
Set fld = tdf.Fields(intJ)
rst.AddNew
rst!TableName = tdf.Name
rst!FieldName = fld.Name
rst!FieldNumber = fld.OrdinalPosition
Select Case fld.Type
Case DB_DATE
rst!DataType = "Date/Time"
Case DB_TEXT
rst!DataType = "Text"
Case DB_MEMO
rst!DataType = "Memo"
Case DB_BOOLEAN
rst!DataType = "Yes/No"
Case DB_INTEGER
rst!DataType = "Integer"
Case DB_LONG
rst!DataType = "Long Integer"
Case DB_CURRENCY
rst!DataType = "Currency"
Case DB_SINGLE
rst!DataType = "Single"
Case DB_DOUBLE
rst!DataType = "Double"
Case DB_BYTE
rst!DataType = "Byte"
Case Else
rst!DataType = "Unknown"
End Select
strDesc = ""
' The Resume Next will avoid an error if there is no Description
strDesc = fld.Properties("Description")
rst!Description = IIf(IsNull(fld.Properties("Description")), "",
strDesc)
rst.Update
Next intJ
End If
Next intI
rst.Close
End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access