Query to text using code

S

Sujith

I have a query(MyQuery) which gets the following, need help in writing
code to convert them to the below structure and saving as text file. Is
this possible? Any help would be highly appreciated.
Thanks,
Sujith

Query:

Name A B C
ASD 20 11 1-Jan-05
BSD 20 5 2-Jan-05
CSD 0 0 3-Jan-05
EFD 20 3 4-Jan-05
GFD 10 22 5-Jan-05


Required Structure on text file:

<data>
<variable name = "MyQuery" >
<row>
<column>Name</column>
<column>A</column>
<column>B</column>
<column>C</column>
</row>
<row>
<column>ASD</column>
<column>20 </column>
<column>11 </column>
<column>01 Jan 05</column>
</row>
<row>
<column>BSD</column>
<column>20 </column>
<column>5 </column>
<column>02 Jan 05</column>
</row>
<row>
<column>CSD</column>
<column>0 </column>
<column>0 </column>
<column>03 Jan 05</column>
</row>
<row>
<column>EFD</column>
<column>20 </column>
<column>3 </column>
<column>04 Jan 05</column>
</row>
<row>
<column>GFD</column>
<column>10 </column>
<column>22 </column>
<column>05 Jan 05</column>
</row>
</variable>
</data>
 
S

SusanV

Create a report from your saved query, then output it to a text file from a
button or whatever:

DoCmd.OutputTo acOutputReport, "MyQueryReport", "MS-DOSText(*.txt)",
"C:\Temp\MyQuery.txt"
 
S

Sujith

SusanV said:
Create a report from your saved query, then output it to a text file from a
button or whatever:

DoCmd.OutputTo acOutputReport, "MyQueryReport", "MS-DOSText(*.txt)",
"C:\Temp\MyQuery.txt"
Click to expand...

I dont get the format that is required by this method
sujith
 
J

John Vinson

I dont get the format that is required by this method
Click to expand...

Then you didn't set up the Report to produce the format that was
required. Susan was suggesting that you craft the Report specifically
to produce that format - a nontrivial task. Did you just use the
Report Wizard? If so, that wasn't what she was suggesting!

John W. Vinson[MVP]
 
S

SusanV

Exactly! Thank you John for clarifying.

SusanV

John Vinson said:
Then you didn't set up the Report to produce the format that was
required. Susan was suggesting that you craft the Report specifically
to produce that format - a nontrivial task. Did you just use the
Report Wizard? If so, that wasn't what she was suggesting!

John W. Vinson[MVP]
Click to expand...
 
S

Sujith

SusanV said:
Exactly! Thank you John for clarifying.

SusanV
Click to expand...
The reason i did not want to use report was that i will have to create
different reports for every query, sorted the problem with a bit of
research on the net using "print" option in vba
Thanks for your quick response
Have a great new year
Sujith
 
Top