Hi folks,

I need to create a text file with a very specific layout from my
access 2003 db.

My coding knowledge is very basic and I am stuck on how to get the
colum names from a table and output them on one line as the header row
for a csv file.

I can't find a way of writing the results of the first loop on a
single line as I have for the second loop. I cannt find a way of
incrementing the "f.Name" statement.

I know how many fields are in the table and this won't vary so in the
second loop i just incremented the "rs.Fields(0)" statement by one
each time, this way I can write it all out on one line.

the end result i am looking for is:

ID, test1, test2, test3,
1,field 1 row 1,field 2 row 1
2,field 1 row 2,field 2 row 2
3,field 1 row 3,field 2 row 3

What I am getting is:

ID,
test1,
test2,
test3,
1,field 1 row 1,field 2 row 1
2,field 1 row 2,field 2 row 2
3,field 1 row 3,field 2 row 3


Here is the code:

Sub test()

Dim rs As Recordset
Dim f As field
Dim db As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("test_table")

Open "C:/my_temp/test.ibe2" For Output As #1

'this is the loop that is driving me crazy

For Each f In rs.Fields
Print #1, f.Name & ","
Next f

'this one works fine

Do Until rs.EOF
Print #1, rs.Fields(0) & "," & rs.Fields(1) & "," & rs.Fields(2)
rs.MoveNext
Loop

Close #1

End Sub

Thanks in advance.

"Geospectrum" <alan.huntley (AT) geospectrum (DOT) co.uk> wrote

The thing you need to know is that the Print # statement automatically
appends a newline if you don't use a trailing semicolon. Try this:

For Each f In rs.Fields
Print #1, f.Name & ",";
Next f

That has fixed it, a simple ; in the right place. I have a long way
to go with programming!!

Thanks very much

On Apr 22, 1:39*am, "Stuart McCall" <smcc... (AT) myunrealbox (DOT) com> wrote:

Geospectrum wrote:
your controls as well.
Sub Test()
Dim rst As Recordset
Dim intFor As Integer
Dim strHead As String
Dim strData As String

Dim intRow As Integer
Set rst = CurrentDb.OpenRecordset("Select * From Table1")

If rst.RecordCount > 0 Then
Do While Not rst.EOF()
strData = ""

For intFor = 0 To rst.Fields.Count - 1
If intRow = 0 Then strHead = _
strHead & rst.Fields(intFor).Name & ", "

strData = strData & rst(intFor).Value & ", "
Next

'the -2 removes the trailing comma.
If intRow = 0 Then
Debug.Print Left(strHead, Len(strHead) - 2)
Debug.Print Left(strData, Len(strData) - 2)
intRow = 1
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing

MsgBox "Done"
End Sub