Solved

Code to export to a text file from access

Posted on 2004-08-10
8
495 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
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11762781
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now