Prev: IP-based viewer for any access mdb database
Next: Switch Between Form View and Datasheet View
From: Geospectrum on 21 Apr 2010 16:54
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.
From: Stuart McCall on 21 Apr 2010 19:39
"Geospectrum" <alan.huntley(a)geospectrum.co.uk> wrote in message
news:d41be056-8356-4352-8e95-c3baaeb3cab0(a)u34g2000yqu.googlegroups.com...
> 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.

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


 | 
Pages: 1
Prev: IP-based viewer for any access mdb database
Next: Switch Between Form View and Datasheet View