Export MS Access Table Description to CSV

Sometimes I'm forced to use Microsoft Access. In this case it's MS Access 2007. It's usually a bad day when this happens (thunderstorms, earthquakes, or wild fires). And I usually smash something when I get home to make me feel better.

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)
   Next intCnt
   Close #OutputFile
Exit_ExportTableDetailsToCsv:
   ExportTableDetailsToCsv = True
   Exit Function
Err_ExportTableDetailsToCsv:
   MsgBox "Error Duder: " & Err.Description & " (" & Err.Number & ")"
   On Error Resume Next
   Close #OutputFile
   ExportTableDetailsToCsv = False
End Function




Good Luck!!!

Over and Out