Access

Every now and then I face some exposure to MS Access. And if it comes to exporting data, I get annoyed by the DoCmd.TransferText method 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:
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
Recent Changes
Excel
Last updated 1 year ago