Solved

Code to export to a text file from access

Posted on 2004-08-10
8
521 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:indyng
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 11762514
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
 
LVL 1

Author Comment

by:indyng
ID: 11762664
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
 
LVL 34

Accepted Solution

by:
flavo earned 225 total points
ID: 11762730
DoCmd.TransferText acExportDelim, "", "MyTable", "C:\MyFile.txt", 0, ""

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

Dave
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11762781
0
 
LVL 34

Expert Comment

by:flavo
ID: 11762797
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
 
LVL 16

Assisted Solution

by:PaulCaswell
PaulCaswell earned 25 total points
ID: 11763098
Export a Query instead of the table.

Paul
0
 
LVL 1

Author Comment

by:indyng
ID: 11763821
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
 
LVL 34

Expert Comment

by:flavo
ID: 11769386
use

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

Dave
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question