Can this be done in Access???

[John Jessen]

Via ODBC I have connected to a MySQL database. I need to extract data into a
csv-file, in such a way that there will be one line with order header info,
one or more ordrelines, one header, one or more order lines, etc. The
content of the "Hedader" and "Line" lines are different.

Can this be done in MS-Access? If not I would appreciate advise on what kind
of tool that could do this job.

John

[Rich P]

Yes. Using DAO objects this code will do the trick. Say you have a
data table with 3 fields (DAO starts enumerating at 0, so 3 fields would
be 0, 1, 2)

Sub xyz()
Dim DB as DAO.Database, rs As DAO.Recordset
Set DB = CurrentDB
Set RS = DB.OpenRecordset("yourDataTable")
Open "C:\yourCSV.csv" For Output As #1
Print #1, rs(0).Name & "," & rs(1) & "," & rs(2)
Do While Not rs.EOF
Print #1, rs(0) & "," & rs(1) & "," & rs(2)
rs.MoveNext
Loop
Close #1
rs.Close
End Sub

rs is a recordset object based on your data table. I delimit each field
in the recordset object with a comma (thus a cvs file is created with 3
columns) using ",". The first Print #1 prints the column Names on the
first row of the csv file. The next print #1 statement will print each
row of data in your table for each rs.MoveNext. There is more
information in the Access Help Files on the "Open" keyword, and "Output"
and "Print". Note: "Print" does not refer to printing to a printer in
the Access Help files. It refers to printing to a text file (or csv
file, same thing). You can also use the Write method, but that includes
double quotes separating each column and doesn't delimit each
column/field like Print. Write will write each line out like one
continuous string (with the delimiter included - except not delimited
like Print).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!