Output a table to a .txt file

[Zygor]

I'm trying to loop through a tables records and output them into a .txt file using the "Open FileName For Input As" way.

I tried the transfertext route and it takes an incredible amount of time (45 + minutes) so I thought I'd give this a go. But I can't seem to get the correct code.

Can anyone help?

 

[bboffin]

It must be a huge table if TransferText is taking that long and any other method is likely to take a similar amount of time.

However you will have to open the .txt file for Output not Input otherwise you can't write to it.

 

[Zygor]

The table has approx. 200,000+ records in it. Not too big by most standards.

What I want is to create an output file, and output each record to it. Do you have a code sample to loop through the table (via a recordset maybe?) and write the lines? I'll tweek as needed. Thanks

 

[bboffin]

The number of records may not be the problem if there is a large number of fields per record.

Try this code but it will be a lot slower than TransferText.

Dim lRS as Recordset
Dim lData as String
Open "MyDataFile.txt" for Output as #1
Set lRS = CurrentDB.OpenRecordset("TableName")
With lRS
   Do Until .EOF
      lData = !Field1 & ","
      lData = lData & !Field1 & ","
      .
      .
      .
      lData = lData & !Fieldn
      Print #1, lData
      .MoveNext
   Loop
End With
Close #1

If your text values may contain commas you should enclose the value in double quotes as well.

 

[Zygor]

Maybe I'm barking up the wrong tree.
I have 24 fields in my table. I know the server isn't causing the transfertext routine to go slow because I copyied the table locally to my C: drive and it's still running 45 minutes or so. The finished .csv file size is about 48,000k. I tried .csv first as that is the file I really want.

I believe you about the output file being slower. Any other thoughts then?

 

[lewds]

Hi...

Just curious..
What OS are you running,what else may be running in the background,and what kind of machine ?

I just did a test with DoCmd.TransferText.....

on a table with 275,342 records of 31 fields over the network and it took just under 14 minutes

I'm running a Netvista 1.6 Ghz /384MB RAM / Windows2K/Office2K

 

[RDWilson2]

what _I_ am really curious about is why you feel compelled to recreate the TrasnferText functionality by "manually" looping through the records and outputting them.