Sulprobil
Search…
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!
Last modified 1yr ago
Copy link