Write a recordset to txt file (1 Viewer)

[richary]

Hi

I need to be able to write a forms recordset to a CSV text file. I belive I have to open a file and then loop around the fields, writing each fieldname.

The 1st bit of code (contains the fieldnames themselves) looks like this

    For i = 1 To UBound(arrFieldList)
        Write #1, arrFieldList(i) & ","
    Next i

where arrFieldList is an array of teh fieldnames that I want to export.


Anyway, the problem is that Write #1 always adds a new line as does Print #1. How do I prevent this, so that all my fieldnames are on the same line?

Thanks

 

[workmad3]

not sure if it will work, but perhaps something like

Dim result as String
result = ""
For i = 1 To UBound(arrFieldList)
        result = result & arrFieldList(i)
        if not (i = UBound(arrFieldList)) then 
            result = result & ","
        end if
Next i
Write #1, result

 

[richary]

Ahh, yes. I'll try that.

Thanks

 

[richary]

Thanks Workmad3. It worked great!

 

[workmad3]

No problems... now make sure you understand what it did so you can use it in other situations

 

[modest]

Not the best way to output data, mainly because you're using a string which has a limit. If you hit a lot of data, it will not work. What exactly are you trying to do?

If you're just trying to output a recordset, such as a table, the best way is to use Access's built-in function:
DoCmd.TransferText acExportDelim, , "Your Table Name", "C:\Your Path\Folder\File Name" & ".csv", True

 

[richary]

Actually, I'm trying to output a form's recordset. The form may or may not have a filter attached to it. I'm adapting workmad3's method to step through the recordsetclone property of the form and writing the fields to a file. Apparantly it will be used as a source for later mailmerges.

Is there a way to use TransferText in this situation?