Here's some older vba that will capture info about your tables and put same into a Table Called Data_Dictionary.
From this base, you could create a query to select the fields you want and export to excel.
Good luck.
Code:
'---------------------------------------------------------------------------------------
' Procedure : DocumentTables
' DateTime : 2006-06-09 09:29
' Author : jack
' Purpose : To create documentation for all tables in this database
' where tableName <>MSys* or TableName doesn't start with "~"
'
'**** Note: This routine builds a new table (data_dictionary) from scratch on each execution ****
'
'Fields that are recorded
' table_name varchar(250)
' table_description varchar(255)
' field_name varchar(250)
' field_description varchar(255)
' ordinal_position NUMBER,
' data_type varchar(15),"
' length varchar(5)
' default varchar(30)
'
'
' ' Last Modified:
'
' Inputs: N/A
' Dependency: Calls Function FieldType
'---------------------------------------------------------------------------------------
'
Public Sub DocumentTables()
'Requires function FieldType
10 On Error GoTo Error_DocumentTables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL_Drop As String
Dim strSQL_Create As String
'==
Dim idxLoop As index
'===
'SQL to Delete existing copy of this table
20 strSQL_Drop = "DROP TABLE data_dictionary;"
'SQL to Create the data_dictionary table
30 strSQL_Create = "CREATE TABLE data_dictionary" & _
"(EntryID AUTOINCREMENT PRIMARY KEY,table_name varchar(250),table_description varchar(255), field_name varchar(250),field_description varchar(255)," & _
"ordinal_position NUMBER, data_type varchar(15)," & _
"length varchar(5), default varchar(30));"
40 Set db = CurrentDb()
50 db.Execute strSQL_Drop, dbFailOnError
60 DoEvents
70 db.Execute strSQL_Create, dbFailOnError
80 DoEvents
90 Set rs = db.OpenRecordset("data_dictionary")
100 With rs
110 For Each tdf In db.TableDefs
120 If Left(tdf.name, 4) <> "Msys" _
And Left(tdf.name, 1) <> "~" Then
130 For Each fld In tdf.Fields
140 .AddNew
150 !table_name = tdf.name
160 !table_description = tdf.Properties("description")
170 !field_name = fld.name
180 !field_description = fld.Properties("description")
190 !ordinal_position = fld.OrdinalPosition
200 !data_type = FieldType(fld.Type)
210 !Length = fld.Size
220 !Default = fld.DefaultValue
230 .Update
240 Next
250 End If
260 Next
270 End With
280 MsgBox "Tables have been documented", vbInformation, "TABLES DOCUMENTED"
290 rs.Close
300 db.Close
Exit_Error_DocumentTables:
310 Set tdf = Nothing
320 Set rs = Nothing
330 Set db = Nothing
340 Exit Sub
Error_DocumentTables:
350 Select Case Err.number
Case 3376
360 Resume Next 'Ignore error if table not found
370 Case 3270 'Property Not Found
380 Resume Next
390 Case Else
400 MsgBox Err.number & ": " & Err.Description
410 Resume Exit_Error_DocumentTables
420 End Select
End Sub
Code:
'---------------------------------------------------------------------------------------
' Procedure : FieldType
' Author : Jack
' Created : 3/18/2008
' Purpose : To identify fieldtypes in Access.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Private Function FieldType(v_fldtype As Integer) As String
10 On Error GoTo Error_FieldType
20 Select Case v_fldtype
Case dbBoolean
30 FieldType = "Boolean"
40 Case dbByte
50 FieldType = "Byte"
60 Case dbInteger
70 FieldType = "Integer"
80 Case dbLong
90 FieldType = "Long"
100 Case dbCurrency
110 FieldType = "Currency"
120 Case dbSingle
130 FieldType = "Single"
140 Case dbDouble
150 FieldType = "Double"
160 Case dbDate
170 FieldType = "Date"
180 Case dbText
190 FieldType = "Text"
200 Case dbLongBinary
210 FieldType = "LongBinary"
220 Case dbMemo
230 FieldType = "Memo"
240 Case dbGUID
250 FieldType = "GUID"
260 End Select
Exit_Error_Fieldtype:
270 Exit Function
Error_FieldType:
280 MsgBox Err.number & ": " & Err.Description
290 Resume Exit_Error_Fieldtype
End Function