Abstract

Every now and then I face some exposure to MS Access. And if it comes to exporting data, I get annoyed by the method DoCmd.TransferText which in my humble opinion is a real pain. If you need to refer to an export spec you either face error prone manual maintenance or a comparably high programming effort - especially if your application uses a variable number of fields. I think you are better off by far with this Sub:

Appendix – Table2Csv Programmcode

Please read my Disclaimer.

Sub Table2Csv(Table As String, Filename As String, _
    Optional Delim As String = ",", Optional ShowHeader As Boolean = True)
'Export database table to csv file, optionally with field headers.
'Simpler and safer approach than DoCmd.TransferText plus export specs,
'especially when we face a variable number of fields.
    Dim FileNum As Integer, i As Integer
    Dim MyDelim As String, NextRecord As String
    Dim rs As New ADODB.Recordset	
	
    FileNum = FreeFile	
    Open Filename For Output As #FileNum	
    rs.Open "SELECT * FROM " & Table, CurrentProject.connection	
    If ShowHeader Then
        MyDelim = ""
        NextRecord = ""
        For i = 0 To rs.fields.count - 1
            NextRecord = NextRecord & MyDelim & rs.fields(i).name
            MyDelim = Delim
        Next i
        Print #FileNum, NextRecord
    End If
    Do Until rs.EOF
        MyDelim = ""
        NextRecord = ""
        For i = 0 To rs.fields.count - 1
            NextRecord = NextRecord & MyDelim & rs.fields(i).Value
            MyDelim = Delim
        Next i			
        Print #FileNum, NextRecord			
        rs.MoveNext			
    Loop	
    rs.Close
    Close #FileNum	
End Sub