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.
1
Sub Table2Csv(Table As String, Filename As String, _
2
    Optional Delim As String = ",", Optional ShowHeader As Boolean = True)
3
'Export database table to csv file, optionally with field headers.
4
'Simpler and safer approach than DoCmd.TransferText plus export specs,
5
'especially when we face a variable number of fields.
6
    Dim FileNum As Integer, i As Integer
7
    Dim MyDelim As String, NextRecord As String
8
    Dim rs As New ADODB.Recordset
9
    
10
    FileNum = FreeFile
11
    Open Filename For Output As #FileNum
12
    rs.Open "SELECT * FROM " & Table, CurrentProject.connection
13
    If ShowHeader Then
14
        MyDelim = ""
15
        NextRecord = ""
16
        For i = 0 To rs.fields.count - 1
17
            NextRecord = NextRecord & MyDelim & rs.fields(i).name
18
            MyDelim = Delim
19
        Next i
20
        Print #FileNum, NextRecord
21
    End If
22
    Do Until rs.EOF
23
        MyDelim = ""
24
        NextRecord = ""
25
        For i = 0 To rs.fields.count - 1
26
            NextRecord = NextRecord & MyDelim & rs.fields(i).Value
27
            MyDelim = Delim
28
        Next i
29
        Print #FileNum, NextRecord
30
        rs.MoveNext
31
    Loop
32
    rs.Close
33
    Close #FileNum
34
End Sub
Copied!

Recent Changes

Excel

Last modified 1yr ago

Copy link