Code to export to a text file from access

Hi Experts,

I want to write to a text file either one line at a time or, all at once from a table using code.

How can this be done?

Thanks
LVL 1
indyngAsked:
Who is Participating?
 
flavoCommented:
DoCmd.TransferText acExportDelim, "", "MyTable", "C:\MyFile.txt", 0, ""

                                                                                                ^^^ change true to false ( -1 = true, 0 = false)

Dave
0
 
flavoCommented:
A Q very similar just got asked.  save me writing it out

http://www.experts-exchange.com/Databases/MS_Access/Q_21088090.html

Dave
0
 
indyngAuthor Commented:
That included the headers in the export, I am using the export file to populate fields in a WMS and the header is causing problems. I also get the first column which I don't want.

Is there a way to limit what gets exported?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
0
 
flavoCommented:
ohh.. first row.. yes, you can use file IO

exaple

Add a refrence to Microsft DAO Obj. Library 3.?? - in VB window select Tools Refrences and tick it from the list

Function ExportMyFile()

Dim rs as dao.recordset

Set rs = db.openrecordset("tblMyTableName")  'open recordset of table

open "c:\myText.txt" for output as #1 'open text file for output of results

If rs.recordcount = 0 then ' no records found  exit
   exit function
end if

'make sure at first
rs.movefirst
'goto second record
rs.movenext

while not rs.eof
   Print #1, rs(0) & ", " & rs(1) & ", " rs(2) '... copy for each field for exaple if tblMyTable has 5 fields use rs(0) ... rs(4)
    rs.movenext
wend

Close #1

rs.close
set rs = nothing

End function


Hope this is ok, did it without vb.

Dave
0
 
PaulCaswellCommented:
Export a Query instead of the table.

Paul
0
 
indyngAuthor Commented:
I'm using this solution

    DoCmd.TransferText acExportDelim, "", "Print", "c:\import.txt", 0, ""

where Print is a query to avoid the problem of extra columns.

Simple seems better as everything else gave me various problems.

SAP our WMS default imports from c:\import.dat

Can I export a .dat file, or change the .txt to a .dat automatically after exporting. if I get a yes or no answer I'll post another question on how to do it so that points can be awarded to the initial question and this one.

Thank you all
0
 
flavoCommented:
use

DoCmd.TransferText acExportDelim, "", "Print", "c:\import.txt", 0, ""
'wait for it
Doevents
Rename "c:\import.txt", "c:\import.dat"

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.