Solved

Code to export to a text file from access

Posted on 2004-08-10
8
513 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

820 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