I imported some CSV files from Microsoft Dynamics AX and proceeded to enter field descriptions for each field. My feeling was this way I'd have a list of column names and could use MS Access documenter to export the meta data to excel for our business analysis. But Access has no easy way of simply copying the table design and pasting it into excel. In fact as the below screen shot shows, Access even gives you the Copy option in design view yet it will not actually copy anything to the clipboard when you select it.
So I kicked an screamed and then decided to Get er Done in VBA and the following is what I came up with. For the time being I am only grabbing column name and description but you could add anything you wanted to this output CSV file. Keep in mind if the description is missing this code will fail with an error. I trust you are bright enough to put in some checks if you actually want to use this.
Option Compare Database
Const ROOT_PATH As String = "C:\Royal Canadian Mint\ERP Data File Descriptions\"
Public Function ExportTableDetailsToCsv(strTableName As String) As Boolean
On Error GoTo Err_ExportTableDetailsToCsv
Dim intCnt As Integer ' create an iterator
Dim intMax As Integer ' create a max len variable
Dim OutputFile ' create a long int to hold file pointer
Dim strFilePath As String ' create variable to hold file path info
strFilePath = ROOT_PATH & strTableName & ".csv" ' assign file path
OutputFile = FreeFile ' get the long for a free file
If Dir(strFilePath, vbNormal) <> "" Then Kill (strFilePath) ' if the filepath exists then kill the existing file
Open strFilePath For Output As #OutputFile ' open the output file
intMax = CurrentDb.TableDefs(strTableName).Fields.Count - 1 ' get max length
For intCnt = 0 To intMax ' note we do this rather than for each because CurrentDb.TableDefs("").Fields cannot be for each looped over
' add whatever meta data that we want to the file
Print #OutputFile, Chr(34) & CurrentDb.TableDefs(strTableName).Fields(intCnt).Name & Chr(34) & ", " & Chr(34) & CurrentDb.TableDefs(strTableName).Fields(intCnt).Properties("Description") & Chr(34)
ExportTableDetailsToCsv = True
MsgBox "Error Duder: " & Err.Description & " (" & Err.Number & ")"
On Error Resume Next
ExportTableDetailsToCsv = False
Over and Out