Export All tables to txt file, filedseparator="|" , textqualifies=None,No Headings

[Sanchi Sharma]

Hi All,

I am new to Access Macros and to this forum.
I want to export all the tables of Ms Access as txt file with fieldseparator ="|", textqualifies= none and with No Headings.
I have created a Export Specification and as the heading of all the tables were different created query SELECT * FROM MSysIMEXColumns and deleted all the data from it. Because of which my Export Specification is working perfect but i am still getting the Headings in my text files.
My Code:
Public Sub Macro1()
Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String
out_file = CurrentProject.Path & "\"
Set db = CurrentDb()
MsgBox "Txt file be saved at Database Path", vbOKOnly, ""
For Each td In db.TableDefs
If Left(td.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, "Sanchi2", td.Name, out_file & Replace(td.Name, "TblOut_", "") & "_Actual" & ".txt", False, ""
End If
Next


End Sub

Looking forward to all your inputs to help me with this. Thanks in advance

[Rawb]

The only option I can think of (other than writing your own custom VBA Export functions) would be to make multiple Export specifications - one for each Table - and give them the same name as the Table. Then you can change your DoCmd.TransferText to the following:

DoCmd.TransferText acExportDelim, td.Name, td.Name, out_file &  Replace(td.Name, "TblOut_", "") & "_Actual" & ".txt", False, ""

The problem there is that if you had 100 Tables, you'd need to make 100 Export Specifications :/

[Sanchi Sharma]

Thanxx Rawb.... have really liked your idea but yes it will be difficult if there are many tables..
can you please tell me how to write the custom VBA export function....

[Rawb]

Well, the short version is this: You'd open each file, one at a time. Then you'd read each file one line at a time, splitting it on the "|" character. Once you've read and split an entire line, you'd write the data to the appropriate Table (probably determined by the file name you're importing?).

To get started, I'd take a look at the WSH ReadLine Method.